CODEWITHSUNDEEP
sqlt-sqldynamic-sql
user1984019
user1984019

Reputation: 3

Can i change input parameter value on execute sp_executesql?

Anyone knows if it is possible to change an input variable during the execution of sp_executesql?

For example, i want to change @var value to 'bye'

declare @var varchar(10) = 'hello'
declare @Query nvarchar(max) = 'print @var set @var = ''bye'' print @var'

EXEC SP_EXECUTESQL @Query, N'@var varchar(10)', @var -- Execute Query Statement

print @var

The last time I do print @var, I was expecting that it prints 'bye', but printed 'hello'.

Can achive my aim without declaring output variables?

Thanks in advance

Upvotes: 0

Views: 1443

Answers (1)

Andomar
Andomar

Reputation: 238086

You'd have to use an output parameter instead:

declare @var varchar(10) = 'hello'
declare @Query nvarchar(max) = 'print @var set @var = ''bye'' print @var'
EXEC SP_EXECUTESQL @Query, N'@var varchar(10) output', @var output
print @var  -- This prints bye

Note that the output is both in the variable declaration, and the actual parameter to sp_executesql.

Upvotes: 2

Related Questions