@@ROWCOUNT - Puzzled?

[ShankarJ]

Hi!

Any idea why the following SP does not return the Row Count. It does return the result set though.

CREATE PROC dbo.sp_DFD_Query ( @SelectQuery NVARCHAR(4000), @SelectedRows INT OUTPUT )
AS
exec sp_executesql @SelectQuery
SET @SelectedRows=@@rowcount
GO

Regards

 

[ShankarJ]

Hi!

Resolved.

Regards

 

[ESquared]

Was it because when you called the procedure you didn't specify OUTPUT?

Look at this:

CREATE PROC dbo.sp_DFD_Query @SelectQuery nvarchar(4000)
AS 
EXEC sp_executesql @SelectQuery
RETURN @@Rowcount 
GO

DECLARE @SelectedRows int
EXEC @SelectedRows = dbo.sp_DFD_Query 'SELECT Blah FROM Gorp'
PRINT @SelectedRows

Also, not to be pedantic, but you're not handling errors in any meaningful way. So back to the OUTPUT parameter... two return two values your only options are the RETURN value and an OUTPUT parameter, or two OUTPUT parameters.

Here's a stab at it:

CREATE PROC dbo.sp_DFD_Query @SelectQuery nvarchar(4000), @SelectedRows int OUTPUT
AS 
DECLARE @Error int
SET @SelectQuery = @SelectQuery + ' SELECT @Error = @@Error, @SelectedRows = @@RowCount'
EXEC sp_executesql @SelectQuery, N'@Error int OUTPUT, @SelectedRows int OUTPUT', @Error OUTPUT, @SelectedRows OUTPUT
SET @Error = IsNull(@Error, @@Error)
RETURN @Error
GO

DECLARE
   @SelectedRows int,
   @ReturnCode int
EXEC @ReturnCode = sp_DFD_Query 'SELCET Balh FORM Grop', @SelectedRows OUTPUT
IF @ReturnCode <> 0 BEGIN
   --Handle Error Here
	PRINT 'Error'
	PRINT @ReturnCode
END
ELSE BEGIN
	PRINT 'Selected Rows'
	PRINT @SelectedRows
SET NOCOUNT ON
END

 

[ShankarJ]

Hi Esquared,

You are right about the OUTPUT bit. When I was trying it out from the Query Analyzer I forgot to mention that and that stumped me.

Regarding the Error Handling, this was just a test procedure of using a shell to execute a SP and return the result set and rowcount. Since this is called by an Win32 application, the error handling will be taken care there.

I do appreciate your comments though as I do use OUTPUT parameters to return Error Messages to the applications.

Regards