rowcount not returning. - 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 2005 Forums

Transact-SQL (2005)

rowcount not returning.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 00:21:38

Hi.
I'm trying to print the @@rowcount of a delete sp operation but i only get zero as result

delete from operations where operations.id in(SELECT operations.id  
FROM         drugsoperations RIGHT JOIN
                      operations ON drugsoperations.operationsid = operations.id
where drugsoperations.id is null)

if @@rowcount > 0
Begin
set @operationscount=@@rowcount
Print @operationscount
End
Else 
Begin
return 99
End

@operationscount declared as bigint

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 00:33:01

@@rowcount only available immediately after the query. It is reset after the if statement
Assign to the variable immediately after the query


delete from operations where operations.id in(SELECT operations.id  
FROM         drugsoperations RIGHT JOIN
                      operations ON drugsoperations.operationsid = operations.id
where drugsoperations.id is null)

set @operationscount=@@rowcount

if @@operationscount> 0
Begin
Print @operationscount
End
Else 
Begin
return 99
End

KH
[spoiler]Time is always against us[/spoiler]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-17 : 00:33:23

You need to get the @@rowcount into a variable before you use it in any logic. The value of @@rowcount is reset after every statement.


delete from operations where operations.id in(SELECT operations.id  
FROM         drugsoperations RIGHT JOIN
                      operations ON drugsoperations.operationsid = operations.id
where drugsoperations.id is null)
If @@rowcount > 0
	Select @operationscount = @@rowcount
else
	Select @operationscount = 99


return @operationscount

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 00:33:56

Ok, i'm trying right now, will let you know...

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 00:37:46

Worked nicely.
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources