is there anything wrong with this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

is there anything wrong with this?

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2002-07-29 : 21:17:10

declare @count int

@count =select count(*) from mytable

print @count

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-29 : 21:18:29

set @count =select count(*) from mytable

You could also:

select @count=count(*) from mytable

Saeed
Starting Member

39 Posts

Posted - 2002-07-29 : 22:14:02

Thanks..
I just found out if I do a select..... and check for @@ROWCOUNT
it would tell me how many rows were selected.

example:
select * from POItem a,#POData b
where
a.POItemN =b.RowPOItem and
a.PurchaseOrderN = b.OldPO

if @@ROWCOUNT >0
begin
goto skip1
end

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-30 : 03:57:55

You will find that in longer procedures and those that modify data where you are also checking @@error that it becomes necessary to store your rowcounts and errors in local variables in order to avoid if statements resetting rowcounts. i.e.

select * from Northwind..Customers
If @@error<>0 do something
If @@rowcount=0 do something

In this pseudo code example @@rowcount will always be 0 becauses it's reset by the If @@error statement. The same applies the other way round i.e. @@error is reset by a succesful If @@rowcount=0 statement.

If you trap these "global" variables in local variables you will then be able to act on them accordingly

declare @err int ; set @err=0
declare @cnt int ; set @cnt=0

select * from Northwind..Customers

select @err=@@error,@cnt=@@rowcount

If @err<>0 do something
If @cnt=0 do something

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources