count for updation procedure - 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)

count for updation procedure

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-09 : 15:09:47

I have a stored procedure just an update statement and I want to know the count of how many times the update is happening in the procedure.Is that possible

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 15:13:36

how may times you get to a section of code??? couldn't you just use a counter variable

Declare @counter int
Set @counter = 0

blah...
Set @counter = @counter + 1
Update....
blah...

If I'm way off, show some code or at least an example...

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-09 : 15:23:12

I was implying something by which I will know how many records have been updated by when the stored procedure is fired like for example if we use the @@IDENTITY we will get the last updated or inserted identity.something like that which can used to get the number of records updated when a stored procedure is fired.So I will know many records have been updated.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 15:39:05

Select Updated = @@RowCount

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-09 : 15:42:28

declare @sql nvarchar(128), @rc int

set @sql="Update tbl_emp_numbers Set emp_number='5047' where emp_number='0000045047'or emp_number='0000045039'"
exec sp_executesql @sql
set @rc=@@rowcount

print @rc

Iam getting error for this :
Invalid column name 'Update tbl_emp_numbers Set emp_number='5047' where emp_number='0000045047'or emp_number='0000045039''.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 15:55:51

not so sure it works like that...

you will need to include the @@rowcount call in your sql string.

You will also need an output variable to get it back... I'll be back in a few with the syntax... i forgot

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 16:05:30

Declare @sqlStr nvarchar(1000),
@params nvarchar(1000),
@rowCount nvarchar(20)

Set @sqlStr = 'Your Query here....'
Set @sqlStr = @sqlStr + ' Select @rowCount = @@rowCount'

Declare @p1 int

Set @params = '@rowCount nvarchar(20) output'
exec master..sp_prepare @p1 output, @params, @sqlStr

exec master..sp_execute @p1, @rowCount output

Select rowCount = @rowCount

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-10 : 18:30:47

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources