Getting Rowcount Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting Rowcount Value

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-12 : 13:50:45

Hi all,
I have the query like

Declare @Cnt Int
Exec Dbo.Pro
Select @Cnt = @@rowcount

The Exec Dbo.Pro Returns 8 records
but @Cnt Shows Only One record.

How can i get the total record count

Thanks
Krishna

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-12 : 13:52:24

the @@rowcount should be where the actual query is. in your case it shoul dbe in the stored proc 'Pro' and should be returned as an OUTPUT parameter.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-12 : 14:07:49

I want to Get the Count of Records returned by the procedure PRO. I have output variable for that count. Is there any other way..?

Krishna

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-12 : 14:39:25

Can you post the code you have for proc Pro..the count has to come from the proc. Theres nothing you can do from outside the proc.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-12 : 14:44:27

if you use INSERT INTO ... EXEC and put the results of the stored proc into a table, you can then retrieve the row count.

Example:

-- step up:
create procedure test
as
select 1 as x union all select 2 union all select 3

go

create table #tmp (x int)

go

-- test it:

insert into #tmp
exec test

select @@rowcount

-- clean up

drop table #tmp
drop proc test

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-12 : 14:52:09

Sure Jeff..
Wouldnt it be easier to get the count using @@ROWCOUNT from the actual proc itself rather than creating a temptable and inserting the rows into it.

Now, if there is a limitation on modifying the inner proc, yes thats the only way to do it. I was trying to steer away from this solution as its an additional overhead of creating the temp table where you can directly get the count from the built-in function

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-12 : 15:18:27

I totally agree 100% ... I just didn't know if he could modify the existing proc or not.

- Jeff
http://weblogs.sqlteam.com/JeffS

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-12 : 15:37:03

Hi guys Thanks for your suggesion But I dont want to go for #table. In My db Having Million of hits
My Code
Alter PROCEDURE DBO.SearchUserMCv1
@USERID INT = NULL ,
@M_Gender int = NULL ,
@P_GenderGenderSeek int ,
@P_LAge int = 18 ,
@P_UAge int = 103 ,
@P_PostalCode VARCHAR(25) = Null ,
@SearchStatus VARCHAR(30) = 'norecords' Output,
@P_SearchName VARCHAR(25) ,
@P_LHeight smallint = 9400 ,
@P_UHeight smallint = 27200
AS
Begin
DECLARE @GUID UNIQUEIDENTIFIER ,
@P_GeoTypeId Int ,
@Country SMALLINT ,
@P_State SMALLINT ,
@P_City SMALLINT ,
@UseDefaultDistance Bit ,
@strQuery nvarchar(4000) ,
@TotalRecords int ,
@NoOfRecordsPerPage Int

Select @NoOfRecordsPerPage = 5

SELECT @GUID = NewId()
SELECT @UseDefaultDistance = 0
Select @P_GeoTypeId = 0,
@Country = 0,
@P_State = 0,
@P_City = 0,
@UseDefaultDistance = 0

SELECT @Country = CountryCode
FROM Mcore.dbo.geozipcode
WHERE Zipcode = @P_PostalCode

SELECT @P_GeoTypeId = Case when (@Country = 0 and @P_State =0 and @P_City =0 and @P_PostalCode = 0) then 0
when (@Country > 0 and @P_State =0 and @P_City =0 and @P_PostalCode = 0) then 1
when (@Country > 0 and @P_State >0 and @P_City =0 and @P_PostalCode = 0) then 2
when (@Country > 0 and @P_State >0 and @P_City >0 and @P_PostalCode = 0) then 3
when (@Country > 0 and @P_State >0 and @P_City >0 and @P_PostalCode > 0) then 4
when (@Country > 0 and @P_State =0 and @P_City =0 and @P_PostalCode > 0) then 5
End

Select @P_GeoTypeId = Isnull(@P_GeoTypeId,0)

EXEC ProfileReadCode.dbo.SearchUberv6
@SearchName = @P_SearchName -- 'OneWay',
,@GenderGenderSeek = @P_GenderGenderSeek -- 4,
,@LAge = @P_LAge -- 18,
,@UAge = @P_Uage -- 99,
,@LHeight = @P_LHeight -- 9400,
,@UHeight = @P_UHeight -- 27200,
,@SearchGeoTypeID = @P_GeoTypeId -- 5,
,@CountryCode = @Country -- 1,
,@PostalCode = @P_PostalCode -- '75063',
,@Distance = 50 -- @P1 output,
,@UseDefaultDistance = 0
,@PhotosOnly = 1
,@OnlineNow = 0
,@SearchBlock = 0
,@SID = @GUID
-- I want return My total count of the SearchUberv6

Select @SearchStatus = 'sucess'

EXITHANDLER:
SELECT @SearchStatus

RETURN

End

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-12 : 15:42:21

This is probably the 3rd or 4th time I am repeating myself.

(1) Go into the proc 'SearchUberv6'
(2) declare parameter as : @RC INT OUTPUT in the parameter list for the proc
(3) Add the line of code: SELECT @RC = @@ROWCOUNT
right after the SELECT statement for which you want to get the rowcount
(4) Modif your parent proc to retrieve the @Rc as follows:

Declare @RCOut INT
EXEC ProfileReadCode.dbo.SearchUberv6
@SearchName = @P_SearchName -- 'OneWay',
,@GenderGenderSeek = @P_GenderGenderSeek -- 4,
,@LAge = @P_LAge -- 18,
,@UAge = @P_Uage -- 99,
,@LHeight = @P_LHeight -- 9400,
,@UHeight = @P_UHeight -- 27200,
,@SearchGeoTypeID = @P_GeoTypeId -- 5,
,@CountryCode = @Country -- 1,
,@PostalCode = @P_PostalCode -- '75063',
,@Distance = 50 -- @P1 output,
,@UseDefaultDistance = 0
,@PhotosOnly = 1
,@OnlineNow = 0
,@SearchBlock = 0
,@SID = @GUID
,@RCOUT = @RC OUTPUT
-- I want return My total count of the SearchUberv6

SELECT @RCOUT

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-04-12 : 15:52:10

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources