Home Setting a parameter using dynamic SQL
Reply: 1

Setting a parameter using dynamic SQL

user3482471
1#
user3482471 Published in 2017-11-13 09:09:44Z

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
2#
gotqn Reply to 2017-11-13 11:04:36Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.329227 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO