sp_executesql and output parameters errors

Question

sp_executesql and output parameters errors

fasttrack

SSCertifiable

Points: 6071
More actions
June 7, 2006 at 8:58 am

#113316

Hi,
I have a problem retrieving output parameters from calling procedura with
declare @OutputParameter varchar(100) ,
        @OutputParameter2 varchar(100) ,
        @StoreProc varchar(128) ,
        @StoreProcCall nvarchar(128) ,
        @rc int
select @StoreProcCall = 'exec ' + @StoreProc + ' @OutputParameter1 output, @OutputParameter2 output'
exec @rError = sp_executesql @StoreProcCall, N'@OutputParameter varchar(100) output', @OutputParameter output, N'@OutputParameter2 varchar(100) output', @OutputParameter2 output

The problem is that running it, i have this error in query analyzer:

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable OutputParameter2 .
OuuPuts are already declared !

Anyone can help me ?
Thank

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

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

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

The issue is the 2 levels of indirect references to exec sp_executeSQL. Let's see the final statement that is executed - each level of indent indicating another context

declare @OutputParameter varchar(100) ,

@OutputParameter2 varchar(100) ,

@StoreProc varchar(128) ,

@StoreProcCall nvarchar(128) ,

@rc int

exec @rError = sp_executesql @StoreProcCall [...]

**** whoops - no output parameters defined at this level!

exec sp_executeSQL , @OutputParameter1 output, @OutputParameter2 output

, @OutputParameter1 output, @OutputParameter2 output

The OutputParameter1 and 2 values are not defined in the context of the second EXEC call. Thus, the error.

Why not just one level of EXEC?

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

I have re-written the statement,

declare @OutputParameter varchar(100) ,

@OutputParameter2 varchar(100) ,

@StoreProc varchar(128) ,

@StoreProcCall nvarchar(128) ,

@rc int

select @StoreProcCall = 'exec @rError=' + @StoreProc + ' @OutputParameter1 output, @OutputParameter2 output'

exec sp_executesql @StoreProcCall, N'@OutputParameter varchar(100) output, @OutputParameter2', @OutputParameter , @OutputParameter2

it is executed without error, but I do not retrieve output, they are null ???

Thank

brendt hess Hall of Fame Points: 3256 More actions · Answer 3

Okay - let's break this down.

exec @rError = @StoreProc @OutputParameter1 output, @OutputParameter2 output

Run just this segment, and give the values in @rcError, @outputparameter1, and @outputparameter2.

--------------------------------------------------------------

Declare @StoreProc varchar(64)

SET @StoreProc = '[whatever]'

Declare @OutputParameter varchar(100)

Declare @OutputParameter2 varchar(100)

Declare @rError int

exec @rError = @StoreProc @OutputParameter output, @OutputParameter2 output

Select @rError As ReturnVal, @OutputParameter as OutParm1, @OutputParameter2 as OutParm2

--------------------------------------------------------------

What values are returned?

stax68 SSChampion Points: 11711 More actions · Answer 4

create

proc testprocparams @OutputParameter1 int output, @OutputParameter2 int output

as

select @OutputParameter1 = 1, @OutputParameter2 = 2

go

declare

@OutputParameter int,@OutputParameter2 int ,@StoreProc varchar(128) ,

@StoreProcCall nvarchar(128),@rc int

select @Storeproc = 'testprocparams',@StoreProcCall = 'exec ' + @StoreProc + ' @OutputParameter1 output, @OutputParameter2 output'

-------------

exec

@rc = sp_executesql @StoreProcCall, N'@OutputParameter1 int output, @OutputParameter2 int output', @OutputParameter output, @OutputParameter2 output

-------------

select @OutputParameter, @OutputParameter2, @rc

go

drop proc testprocparams

go

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

Thank to everybody,

I'll try your suggestion ...

thank

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

OK,

I have resolved the problem, I forgot to add OUTPUT after the OutPutParameters.

Thank