Setting a parameter using dynamic SQL

Question

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.

gotqn · Accepted Answer · 2017-11-13 11:04:36Z

4

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;

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.

edited Nov 13, 2017 at 11:04

answered Nov 13, 2017 at 9:18

gotqn

44k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting a parameter using dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related