Simple T-SQL..

Please complete the following T-SQL per comments included.

Thank you.

SELECT
      CustomerID
      , FirstName
FROM tblCustomers
WHERE FirstName = 'Mike'

may return none, one, or multiple records. I want to findout how many. I know I can do something like:

SELECT Count(*) FROM tblCustomers WHERE FirstName = 'Mike'

but I am interested in finding out how a recordset/dataset object could be utilized here.

DECLARE @ID AS int                 -- store CustomerID in @ID
DECLARE @numberOfRecords AS int    -- number of records
-- DECLARE ds AS recordset         -- ds to store the select statement shown below??

-- look for CustomerIDs with first name Mike
SELECT 
	CustomerID
	, FirstName 
FROM tblCustomers 
WHERE FirstName = 'Mike'

-- not sure how this is done in T-SQL??
-- ds = SELECT CustomerID, ....

-- store number of records in ds??
-- Set  @numberOfRecords = ds.recordcount 

-- display @numberOfRecords
Select @numberOfRecords;

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
After the select, you simply use @@rowcount variable.
Set @yourvar = @@rowcount
0
DcpKingCommented:
What are you trying to do next? If you are looking at getting a record set for something like VB or C# then these languages have functions that will tell you how many records were returned.

select CustomerID, FirstName from tblCustomers where FirstName = 'Mike'

Open in new window

will get you a list. If you then want to know how many were returned in the list:

select @@rowcount

Open in new window

as the next executed line will give you that.

However, I suspect that you want to do something more if you actually have retrieved records, so you should look at either temporary tables or table variables. Here's an example of the latter:

declare @ta table (CustomerID int, FirstName varchar(32))
insert into @ta
    select CustomerID, FirstName from tblCustomers where FirstName = 'Mike'

--  After that you can do something with the data you retrieved, if you got any:

if @@rowcount > 0
begin
--put your actions here
end

Open in new window


hth

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.