sp_executesql and howto return rowcount

Question

Post reply

sp_executesql and howto return rowcount

candide

Ten Centuries

Points: 1164
More actions
August 18, 2016 at 8:29 am

#329228

Hi,
I'm using dynamic code but I'm not able to return any rowcount
DECLARE @rowDSServernvarchar(50)= NULL
DECLARE @rowDSDatenbanknvarchar(50)= NULL
DECLARE @rowDSTabellenvarchar(50)= NULL
DECLARE @strDSTabelleQualnvarchar(50)= NULL
DECLARE @strDSTabellenvarchar(50)= NULL
DECLARE @ParPRINTER_NAME_LEN8As nvarchar(8) = NULL
DECLARE @SQLstmtAs nvarchar(900) = NULL
DECLARE @RCountAS int = 0
SET @SQLstmt =
N'SELECT [LCPRBSELF]' +
' FROM [' + @rowDSServer+ ']' + '.'
+ '[' + @rowDSDatenbank+ ']' + '.'
+ '[' + @strDSTabelleQual+ ']' + '.'
+ '[' + @strDSTabelle+ ']' +
' WHERE [DRUCKER] = ''' + @ParPRINTER_NAME_LEN8 + '''' + ';' +
' SELECT @RCount = @@ROWCOUNT'
EXEC sys.sp_executesql @stmt = @SQLstmt
, @params = N'@result as INT OUTPUT'
, @result = @RCount OUTPUT;
IF @RCount > 0 ...
The code also uses a cursor but thats not the issue. I just want to return the number of rows from this dynamic SQL-Select.:ermm:
--
candide
________Panta rhei

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

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

ScottPletcher SSC Guru Points: 100957 More actions · Answer 1

Your variable in the code is named @Rcount. You need to use that name in the @params clause:

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@Rcount INT OUTPUT'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

candide Ten Centuries Points: 1164 More actions · Answer 2

hi,

so you mean

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@RCount as INT OUTPUT'

, @result = @RCount OUTPUT;

IF @RCount > 0

This fires error 3178 which means:

"The parameterized query '%.*ls' expects the parameter '%.*ls', which was not supplied."

Where can I find a source to explain the sp_executesql best?

thx

--
candide
________Panta rhei

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You need to declare @result at the start of your code and reverse the order of the variables in the last line. Example C on this page shows you how to do it.

John

ScottPletcher SSC Guru Points: 100957 More actions · Answer 4

SET @SQLstmt =

N'DECLARE @RCount int ' +

' SELECT [LCPRBSELF]' +

' FROM [' + @rowDSServer+ ']' + '.'

+ '[' + @rowDSDatenbank+ ']' + '.'

+ '[' + @strDSTabelleQual+ ']' + '.'

+ '[' + @strDSTabelle+ ']' +

' WHERE [DRUCKER] = ''' + @ParPRINTER_NAME_LEN8 + '''' + ';' +

' SELECT @RCount = @@ROWCOUNT'

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@RCount int OUTPUT'

, @RCount = @RCount OUTPUT;

IF @RCount > 0 ...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

candide Ten Centuries Points: 1164 More actions · Answer 5

hey Scott,

your code slightly changed: added ";" after first DECLARE

When debugging @SQLstmt looks this:

DECLARE @RCount int; SELECT [LCPRBSELF] FROM [SVCECPSIPOS01].[printpos01].[A].[PDS] WHERE [DRUCKER] = 'P513'; SELECT @RCount = @@ROWCOUNT

Throws Error 134

"The variable name '%.*ls' has already been declared. Variable names must be unique within a query batch or stored procedure."

It seems the line

, @RCount = @RCount OUTPUT;

needs a local declare which I added. Now it looks this:

DECLARE @NumberRowsAS int = 0

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@RCount int OUTPUT'

, @RCount = @NumberRows OUTPUT;

Still error 134!:w00t:

--
candide
________Panta rhei

ScottPletcher SSC Guru Points: 100957 More actions · Answer 6

I give up. You keep randomly changing things. And this message:

"The variable name '%.*ls' has already been declared. Variable names must be unique within a query batch or stored procedure."

is useless, since you don't identity the variable in question.

Good luck with this, though.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

candide Ten Centuries Points: 1164 More actions · Answer 7

sorry Scott,

but your code threw error 134 so I just tried to fix it.

But you put me on the right lane. The solution looks like this:

DECLARE @SQLstmtAs nvarchar(500)= NULL

DECLARE @CountRowsAS int = 0

SET @SQLstmt =

N'SELECT @Count = Count (*) ' +

' FROM [' + @rowDSServer+ ']' + '.'

+ '[' + @rowDSDatenbank+ ']' + '.'

+ '[' + @strDSTabelleQual+ ']' + '.'

+ '[' + @strDSTabelle+ ']' +

' WHERE [DRUCKER] = ''' + @ParPRINTER_NAME_LEN8 + '''' + ';'

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@Count int OUTPUT'

, @Count = @CountRows OUTPUT

-- This SELECT statement returns the value of the OUTPUT parameter.

SELECT @CountRows

IF @CountRows > 0

BEGIN

.

END

ALL I needed was the output parameter. The other parameters like @rowDSServer etc. are simply filled long before I get here.:-)

thx

--
candide
________Panta rhei