SQL How to change string variable inside another variable

Question

is it possible to change one by variable by executing another one?

My code now:

DECLARE
@CHOSENID VARCHAR(MAX),
@sql VARCHAR(MAX),
@COLUMNS VARCHAR(MAX)
SET @CHOSENID = '1032, 1132, 332, 1021'
SET @sql = ' select @COLUMNS = COALESCE(@COLUMNS  + '' '', '''') + n.PRODUCT + '' BIGINT NOT NULL DEFAULT -1,'' 
from MYTABLE n where ID IN ('+@CHOSENID+') and TYPE=''X''
SET @COLUMNS = CONCAT(@COLUMNS, ''TIME DATETIME NOT NULL DEFAULT GETDATE() '')'
PRINT @sql
EXEC (@sql)
PRINT @COLUMNS

Error in result:

Must declare the scalar variable "@COLUMNS".

When I tried to declare @COLUMNS inside @sql query can be executed, but PRINT @COLUMNS does not return anything.

ID in MYTABLE is bigint and @CHOSENID is varchar, so I can't simply execute this without adding @sql

Gordon Linoff · Accepted Answer · 2016-05-18 13:54:08Z

2

I think you are trying to return a value. You should be using sp_executesql with an output parameter. Something like this:

DECLARE @CHOSENID VARCHAR(MAX), @sql NVARCHAR(MAX), @COLUMNS VARCHAR(MAX);
SET @CHOSENID = '1032, 1132, 332, 1021';
SET @sql = N'
declare @columns varchar(max);
select @Columns = COALESCE(@COLUMNS  + '' '', '''') + n.PRODUCT + '' BIGINT NOT NULL DEFAULT -1,'' 
from MYTABLE n
where ID IN (' + @CHOSENID+  ') and TYPE = ''X''
';

exec sp_executesql @sql, 'N'@Columns varchar(max) output', @Columns = @Columns output;

print @Columns;

This doesn't do the final concatenation in the final set. It is intended to show how to use sp_executesql.

answered May 18, 2016 at 13:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2016-05-18 14:01:05Z

1

If I understand your attempts correctly there is no need for dynamic SQL at all:

Try this

DECLARE @CHOSENID VARCHAR(MAX)='1032, 1132, 332, 1021';

WITH ChosenIDs AS
(
    SELECT A.B.value('.','int') AS ChosenID
    FROM (SELECT CAST('<x>' + REPLACE(@CHOSENID,', ','</x><x>') + '</x>' AS XML) AS Splitted) AS tbl
    CROSS APPLY Splitted.nodes('/x') AS A(B)
)
SELECT STUFF(
(
    SELECT ',' + n.PRODUCT + ' BIGINT NOT NULL DEFAULT -1' 
    FROM MYTABLE n 
    WHERE ID IN (SELECT ChosenID FROM ChosenIDs) 
      AND [TYPE]='X'
    FOR XML PATH('')
),1,1,'')

Short explanation:

The CTE will first transfer your @CHOSENID into

'<x>1032</x><x>1132</x><x>332</x><x>1021</x>'

Via XML methode .nodes() this is splitted and read as int values. So the CTE returns as a derived table with your numbers.

Instead of SELECT @var=@var + ISNULL(Something,'') FROM... you better use the trick with FOR XML PATH() in order to concatenate values.

As I do not know what exactly you are trying to achieve, this is flying blind through the night... Hope it helps...

answered May 18, 2016 at 14:01

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

3 Comments

Gottfried Lesigang Over a year ago

Hi @Kelk, If it's easy for you (and amount of data is enough to notice a difference), I'd ask you to compare the dynamic SQL approach with my ad-hoc approach in terms of performance. I don't have the time to set up a test scenario on my own at the moment... Thx

Kelk Over a year ago

unfortunately data amount was too small - on hundreds and thousands of records there was nearly no difference neighter in time nor in used memory.

Gottfried Lesigang Over a year ago

@Kelk Thx for testing!

Collectives™ on Stack Overflow

SQL How to change string variable inside another variable

2 Answers 2

Comments

Short explanation:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Short explanation:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related