When working with synapse as a TSQL developer, it does feel like you’ve gone in a time warp back to SQL 7.0 in terms of whats supported and whats not. Luckily most of the TSQL patterns not supported have some form of workaround.
This one I’m looking at is the naughty practice of doing variable assignment inside a select or update statement. We know its always been naughty, but with regular TSQL it just worked – now in Synapse its just not going to work (probably ever).
DECLARE @Columns nvarchar(max)
SELECT @Columns =coalesce(@Columns + c.name + ',', c.Name)
FROM sys.columns c
WHERE c.object_id=object_id('spt_monitor')
PRINT @Columns
When we run this on Synapse we get a message like :
Msg 104473, Level 16, State 1, Line 11
A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.
STRING_AGG() to the rescue. While this is quite a new function it is fully supported on synapse, so we can write TSQL like below:
DECLARE @Columns nvarchar(max)
SELECT @Columns=string_agg (c.name,',')
FROM sys.columns c
WHERE c.object_id = object_id('dbo.MyTable')
PRINT @Columns
