CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
user3482471
user3482471

Reputation: 227

Setting a parameter using dynamic SQL

I have been attempting to set a parameter using dynamic SQL however everytime i do it it is always returning a NULL. Below i have copied a basic version of my code, can anyone advise how i would do what i am looking to do.

DECLARE
@SQL_Prev_Values nvarchar(max),
@Value nchar(100),
@Prev_Values varchar(150) =' '

SET @Value = 'dave'

SET @SQL_Prev_Values = 'SET @Prev_Values = @Prev_Values + ''' + REPLACE(@Value,' ' ,'')+''''
SELECT @SQL_Prev_Values
EXEC sp_executesql @SQL_Prev_values,N'@Prev_Values nchar out',@Prev_Values out 
SELECT @Prev_Values
SET @Prev_Values = @Prev_Values + 'dave'
SELECT @Prev_Values

I have seen things online however they just do not appear to be making sense to me. Any help would be appreciated. Thanks in advance.

Upvotes: 3

Views: 68

Answers (1)

gotqn
gotqn

Reputation: 43666

As you can see from the screen shot below, the template is working perfectly:

DECLARE @Prev_Values nchar;
DECLARE @SQL_Prev_Values NVARCHAR(MAX);

SET @SQL_Prev_Values = N'SET @Prev_Values = 1;'

EXEC sp_executesql @SQL_Prev_values,N'@Prev_Values nchar out',@Prev_Values out

SELECT @Prev_Values;

enter image description here

Try this:

DECLARE
@SQL_Prev_Values nvarchar(max),
@Value nchar(100),
@Prev_Values varchar(150) =' '

SET @Value = 'dave'

SET @SQL_Prev_Values = 'SET @Prev_Values =  @Prev_Values + ''' + REPLACE(@Value,' ' ,'')+''''
SELECT @SQL_Prev_Values

EXEC sp_executesql @SQL_Prev_values,N'@Prev_Values varchar(150) out',@Prev_Values out 
SELECT @Prev_Values

The value of the output parameter was different then the one set in the sp_executesql.

Upvotes: 4

Related Questions