• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Simple T-SQL..

Please complete the following T-SQL per comments included.

Thank you.

      , 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
	, 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

Mike Eghtebas
Mike Eghtebas
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
After the select, you simply use @@rowcount variable.
Set @yourvar = @@rowcount
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
--put your actions here

Open in new window


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now