Update Rowcount

Question

Update Rowcount

TaffyLewis

Ten Centuries

Points: 1033
More actions
December 26, 2007 at 5:48 pm

#185223

Does anyone know a method for returning the number of records effected by an update query being run within a stored procedure. Here would be the code.
Create Procedure dbo.MyTestProd
As
begin
Update dbo.t1 set dbo.t1.f1 = 2
end
I'd thought I'd something like: if there were 10 records, I'd like to get the number 10
Alter Procedure dbo.MyTestProd
@RowCount as int out
As
begin
Update dbo.t1 set dbo.t1.f1 = 2
set @RowCount = @@Rowcount
end
but this is not working!?!
I think there is something I'm not doing right.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

What are you using to call the proc? In other words, let's see the EXEC statement 'cause the code in the proc is mostly correct. Only thing wrong with it is that if no updates take place @RowCount will be NULL instead of "0" because you didn't set it to "0" to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736183

December 26, 2007 at 9:36 pm

#762839

I agree with Jeff. I'd do it like you're doing it.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

I still need to see how you're calling the proc because that might be part of your problem...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 4

Definately. it is all about how you are calling the spoc..

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 5

Alter your proc adding return statement like this

Alter Procedure dbo.MyTestProd @RowCount as int out

As

begin

Update dbo.t1 set dbo.t1.f1 = 2

set @RowCount = @@Rowcount

return @RowCount

end

and now you can execute it and grab the value in any of this two methods (output param or return value). It's all about your choice. Of course, you don't need both in one proc.

DECLARE @rc int

DECLARE @RowCount int

EXEC @rc = [dbo].[MyTestProd] @RowCount OUTPUT

Print '@RowCount = ' + isnull( CONVERT(nvarchar, @RowCount), ' ' )

Print '@RC = ' + isnull( CONVERT(nvarchar, @rc), ' ' )

Mauve SSChampion Points: 11316 More actions · Answer 6

I agree with Nebojsa.

Use a return (OUTPUT) parameter as it is a much cleaner solution. You're calling a procedure and not a function. In coding, procedures are not supposed to return values (as a return argument) while functions do.

I've never liked the SQL Server capability to return application data (limited to integer only) from a stored procedure call. Depending upon the calling mechanism, it could be "stepping on" your value.

So use an output parameter and save the "RC" for execution status.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 7

John, don't escape using return value. Let's see another scenario for proc:

Alter Procedure dbo.MyTestProd @RowCount as int out

As

begin

declare @RetValue int

Update dbo.t1 set dbo.t1.f1 = 2

select @RowCount = @@Rowcount, @RetValue = @@Error

return @RetValue

end

DECLARE @rc int

DECLARE @RowCount int

EXEC @rc = [dbo].[MyTestProd] @RowCount OUTPUT

IF @rc = 0

Print '@RowCount = ' + isnull( CONVERT(nvarchar, @RowCount), ' ' )

ELSE

Print 'Error: @rc = ' + isnull( CONVERT(nvarchar, @rc), ' ' )

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

JohnG (12/27/2007)
I agree with Nebojsa.
Use a return (OUTPUT) parameter as it is a much cleaner solution. You're calling a procedure and not a function. In coding, procedures are not supposed to return values (as a return argument) while functions do.
I've never liked the SQL Server capability to return application data (limited to integer only) from a stored procedure call. Depending upon the calling mechanism, it could be "stepping on" your value.
So use an output parameter and save the "RC" for execution status.

I like it for a couple of reasons... can't update a table from a function as you may have to do with a (ugh!) sequence table and you also have the abilitity to return more than one "status" instead of just the return variable.

But I do agree that, in most cases, using a stored procedure to return a handful of variables instead of a result set smacks of RBAR on steriods and just shouldn't be done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)