ROWCOUNT as Output Parameter -- sql server programming

Sandy
11/4/2006 5:13:01 PM

Thanks for your responses. I figured out the problem. I'm posting it below
for anyone who may be reading this in the future.

Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
-- @RowCount int OUTPUT -- This needs to be taken out
As

Declare @rowcount int -- This needs to be put in
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
Set @RowCount = @@ROWCOUNT -- This needs to come out and be replaced by:
Select @rowcount = @@rowcount
RETURN(@rowcount)

--
Sandy

[quoted text, click to view]

Erland Sommarskog
11/4/2006 7:54:41 PM

Sandy (Sandy@discussions.microsoft.com) writes:
[quoted text, click to view]

One thing you obviously are doing wrong is to not post the code where
you call the procedure.

So all I can say for now is: you are not supplying the required parameter
when you call the procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Arnie Rowland
11/4/2006 10:34:11 PM

Sandy,

That is a 'kludge', but not a robust repair.

You should correct the application to supply the OUTPUT parameter, and NOT
use the RETURN value for data. The purpose of the RETURN value is to provide
operational status (success/failure) information.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

[quoted text, click to view]

Aaron Bertrand [SQL Server MVP]
11/4/2006 11:50:43 PM

No, that is not the solution.

You have changed your @rowcount from an output parameter to a return value.
These are very different, and a return value should not be used to output
data.

The problem does not need to be fixed in the stored procedure code, it needs
to be fixed in the application code that is calling the stored procedure.

[quoted text, click to view]

Uri Dimant
11/5/2006 12:00:00 AM

Sandy
Look at sp_executesql stored procedure does this job for you

DECLARE @table sysname
DECLARE @SQLStringA nvarchar(50)
DECLARE @SQLStringB nvarchar(50)
DECLARE @ParmDefinition nvarchar(100)

SET @table = 'pubs..sales'
SET @SQLStringA =
N'SELECT @c = COUNT(*)'
SET @SQLStringB = ' FROM '+@table
SET @ParmDefinition = N'@c int output'

EXECUTE ('
DECLARE @iCount int
exec sp_executesql N''' + @SQLStringA + @SQLStringB
+''', N''' + @ParmDefinition
+''', @c = @iCount output
select @iCount as [Count]')

[quoted text, click to view]

Erland Sommarskog
11/5/2006 12:00:00 AM

Sandy (Sandy@discussions.microsoft.com) writes:
[quoted text, click to view]

Nah, it's only the solution for anyone who has written their client code
just like you did.

[quoted text, click to view]

That is, you have written the client code to receive the rowcount as the
return value from the stored procedure. While this is perfectly possible
to do, conventionally the return value from a stored proceedure is only
used to indicate success/failure with 0 for success and everything else
for failure. For actual data, you normally use OUTPUT parameters. Return
values are a bit too inflexible:

* There can only be one of them.
* They can only be integer.
* They cannot be NULL.

A further observation is that returning the rowcount separately is a
bit redudant, as the client easily can count the number of rows it
received itself. And the output parameter (or the return value) will
not be available until all rows have been received.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : ROWCOUNT as Output Parameter