CODEWITHSUNDEEP
sqlsql-server
Mohan Babu
Mohan Babu

Reputation: 1

How to view the variable value in sql server

DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #yt) AS Weeks

--execute (@ColumnName)
print @ColumnName

the output is [1],[2],[3]

Can anyone explain how this works

Upvotes: 0

Views: 62

Answers (1)

Charlieface
Charlieface

Reputation: 71263

The way this works is quite simple:

When run in a serial plan (non-parallelized), each row coming out of the SELECT will take the existing value of @ColumnName and concatenate it.

If it is run in a parallel plan, it can return incorrect results due to race conditions between different threads. Therefore, you must use OPTION(MAXDOP 1) in such a query.

In any case, even serial plans are not guaranteed by SQL Server to return correct results, just that people have always observed them to do so, and that may change.

So you are much better off using a proper aggregation function, such as STRING_AGG or FOR XML PATH

Upvotes: 1

Related Questions