0

Someone told me a sentence that I don't need to write Loop(while). for example, I wrote this sentence.

WHILE(@intFrom <= @intTo)
    BEGIN
        SET @strSelectMonthCol = @strSelectMonthCol + ',ISNULL(SUM([M_'+CONVERT(VARCHAR, @intFrom)+']),0) AS M_'+CONVERT(VARCHAR, @intFrom)
        SET @strSelectSumCol = @strSelectSumCol + 'SUM(ISNULL([M_'+CONVERT(VARCHAR, @intFrom)+'],0)) +'
        SET @strPivot = @strPivot + '[M_' + CONVERT(VARCHAR, @intFrom) + '],'
        SET @intFrom += 1
    END

And then he gave me this sentence.

DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY = ''
SELECT @QUERY = @QUERY + '[' + A.X_CD + '], '
FROM ( SELECT DISTINCT TOP 10  X_CD FROM X_ITEM ) AS A
SELECT @QUERY

How does @Query accumulate repeatedly without WHILE?

JaeHoon
  • 1
  • 1
  • 5
    This approach to string concatenation is [not guaranteed to work](https://stackoverflow.com/q/15138593/73226). when it does work the variable assignment happens repeatedly for each row in the input, building up the string. You should use STRING_AGG – Martin Smith Aug 14 '20 at 07:36
  • 3
    If you aren't on SQL Server 2016+, then you can use the "old" `FOR XML PATH` and `STUFF` method. Also, note that concatenation like `... + '[' + A.X_CD + '],' + ...` is *not* safe from injection. YOu should use `QUOTENAME` to safely quote the values: `...+ QUOTENAME(A.X_CD) + ...` Finally, you need to define your lengths, scales, and precisions when using data type. Not doing so can easily end up with unexpected behaviour. [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Thom A Aug 14 '20 at 08:20
  • thanks for answering I'm using the 2016 version so I can't use STRING_AGG. i'm happy to know i shouldn't use like this. – JaeHoon Aug 18 '20 at 00:12

0 Answers0