get @@rowcount without displaying result

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 2005 Forums

Transact-SQL (2005)

get @@rowcount without displaying result

Author

Topic

ronsi
Starting Member

2 Posts

Posted - 2007-06-26 : 02:09:01

Hi...
I have sp like this

create proc spInActiveProduct
as
select proid,proname where prostatus = 0 -- inactive

now i need to know affected row (@@rowcount) of that SP, i try to create sp

create proc spGetRowCount
as
execute sp_sqlexec 'exec spInactiveProduct'
select @@rowcount

The problem is, the result is still displayed.
Is there a way, execute sp without showing the result ? I only need @@rowcount value.

thanks

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-26 : 02:29:18

You need to change your SP design otherwise
create a table variable with same columns as sp_inactiveproduct returns

insert the return value to the table variable and find the count.

--------------------------------------------------
S.Ahamed

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-26 : 02:30:03

Why not just
SELECT COUNT(*) ... WHERE prostatus = 0

or
You can execute the SP into a temporary table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-26 : 02:39:43

Assign the @@rowcount value to a variable for further processing

select @myvar = @@rowcount

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 03:03:57

quote:
Originally posted by pbguy

You need to change your SP design otherwise
create a table variable with same columns as sp_inactiveproduct returns

insert the return value to the table variable and find the count.

--------------------------------------------------
S.Ahamed

You cant use table variable to get output from procedure
Istead use temp table

Madhivanan

Failing to plan is Planning to fail

ronsi
Starting Member

2 Posts

Posted - 2007-06-26 : 03:53:19

ah...ok..temp table...
thanks guys....

Kristen
Test

22859 Posts

Posted - 2007-06-26 : 04:52:59

If it was me I think I would change spInactiveProduct to add an optional parameter that provides no SELECT output, but instead returns an OUTPUT parameter with the RowCount

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 08:51:19

quote:
Originally posted by Kristen

If it was me I think I would change spInactiveProduct to add an optional parameter that provides no SELECT output, but instead returns an OUTPUT parameter with the RowCount

Kristen

Not If that procedure is used by other applications to get data

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-26 : 10:05:17

"Not If that procedure is used by other applications to get data"

Adding a new parameter, with a default value, won't effect anything else though.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources