ROWCOUNT as Output Parameter

[Sandy]

Hello -

I have the following stored procedure and I'm trying to get the rowcount:

Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As

Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
Set @RowCount = @@ROWCOUNT

When I try to execute it, I am getting the following message:

"Procedure or Function 'spLoansAllSearchDate' expects parameter
'@RowCount', which was not supplied"

What am I doing wrong?

Any help will be greatly appreciated!

[Erland Sommarskog]

Post by Sandy
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?

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.

[Arnie Rowland]

Even though @RowCount is denoted as an OUTPUT parameter, the calling code
must provide the @RowCount parameter to 'catch' the output.
--
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

Post by Sandy
Hello -
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?
Any help will be greatly appreciated!
--
Sandy

[Sandy]

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

Post by Sandy
Hello -
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?
Any help will be greatly appreciated!
--
Sandy

[Aaron Bertrand [SQL Server MVP]]

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.

Post by Sandy
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,
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
--
Sandy

Post by Sandy
Hello -
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?
Any help will be greatly appreciated!
--
Sandy

[Arnie Rowland]

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

Post by Sandy
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,
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
--
Sandy

Post by Sandy
Hello -
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?
Any help will be greatly appreciated!
--
Sandy

[Uri Dimant]

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 ('

Post by Sandy
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,
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
--
Sandy

Post by Sandy
Hello -
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
@RowCount int OUTPUT
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC
"Procedure or Function 'spLoansAllSearchDate' expects parameter
What am I doing wrong?
Any help will be greatly appreciated!
--
Sandy

[Erland Sommarskog]

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

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

Post by Sandy
Create Procedure [dbo].[spLoansAllSearchDate]
@SearchFrom datetime,
@SearchTo datetime,
As
Select l.LoanID, l.ApplicationNo, l.ApplicantName, l.DateOfAction,
<blah, blah, blah>
Order by l.DateOfAction ASC

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.