Assign values to variables in stored procedure

Question

 select  @numOfColumns =  count(distinct Col) from #b
 SET @sql2=
'SELECT'+ @columns +'+= QUOTENAME(Col) + '',''
 from (SELECT DISTINCT top @numOfColumns Col FROM #b ORDER BY Col) A';

 EXECUTE sp_executesql @sql2;

I am trying to get this stored procedure to work. Trying to pass @numOfColumns to the statement then assign the values from QUOTENAME(Col) to @columns and Then Exec the statement.

Script

DECLARE 
@columns NVARCHAR(MAX) = '',
@sql     NVARCHAR(MAX) = '',
 @sql2     NVARCHAR(MAX) = '',
@numOfColumns int = 0;

SELECT customerid, curbal,  Col = CAST (ROW_NUMBER() OVER (PARTITION BY 
convert(int,customerid) ORDER BY convert(float,curbal) desc) as int)
into #b
FROM [sav acc]

select  @numOfColumns =  count(distinct Col) from #b

SET @sql2= 'SELECT '+ @columns+' += QUOTENAME(Col) + '','' from (SELECT 
DISTINCT top (@numOfColumns) Col FROM #b ORDER BY Col) A';

EXECUTE sp_executesql @sql2, N'@numOfColumns', @numOfColumns int;

This is origin of post. ORIGINAL POST . The solution did work, But i tested it with the Source data it would positions column at [3], [2], 1 and and I wanted the columns as 1,[2],[3]

Now getting error after adjustment: Msg 245, Level 16, State 1, Line 23 Conversion failed when converting the nvarchar value 'SELECT+= QUOTENAME(Col) + ',' from (SELECT DISTINCT top ' to data type int. — JasonX, Feb 21 '20 at 15:22
@JamesS no, it should be parametrised, not injected. It's a parameter, not a dynamic object. — Thom A, Feb 21 '20 at 15:32
Why the dynamic sql? I don't see anything this code that indicates you need to use dynamic sql. — Sean Lange, Feb 21 '20 at 15:42

GarethD · Answer 1 · 2020-02-21T15:51:41.523

Firstly, the way you are using TOP is pointless. If you have 5 distinct values for Col in #b then SELECT DISTINCT Col FROM #b will only return 5 records, regardless of if you apply TOP 5 or not. It seems you are only using it to allow sorting, but you could just put the sort in the outer query.

Secondly, you should not use variable assignment to concatenate strings, the behaviour is unreliable and undocumented you should use STRING_AGG, or for earlier versions of SQL Server you can leverage the XML extensions, either:

CREATE TABLE #B (Col CHAR(1));
INSERT #B (Col) VALUES ('A'), ('A'), ('B'), ('C');

DECLARE @Columns NVARCHAR(MAX) = 
    (   SELECT  STRING_AGG(QUOTENAME(Col), ',') WITHIN GROUP (ORDER BY Col)
        FROM    (SELECT DISTINCT Col FROM #B) AS b
    );

Or

DECLARE @Columns NVARCHAR(MAX) = 
        STUFF(( SELECT  CONCAT(',', QUOTENAME(Col))
                FROM    #B
                GROUP BY Col
                ORDER BY Col
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

While the above shows that dynamic sql is unnecessary, it would also be unnecessary if you were using @top, you could simply use:

DECLARE  @numOfColumns INT = (SELECT count(distinct Col) from #b);
DECLARE @Columns NVARCHAR(MAX) = '';

SELECT  @Columns += QUOTENAME(Col) + ','
FROM    (SELECT DISTINCT TOP (@numOfColumns) Col FROM #B) AS b
ORDER BY Col;

SELECT @Columns;

As simple as it seems, with the original source, the source data produced different outcome upon execution. When i run code on source data, this part: SELECT @Columns += QUOTENAME(Col) + ',' : stores column as [3], [1], [2]. But in original post, The solution given to me would position them correctly [1], [2], [3]. But This works. Thank you. — JasonX, Feb 21 '20 at 16:15

Assign values to variables in stored procedure

1 Answers1