SqlDW / Synapse – TABLE Variable Compatibility

Azure Synapse SQL does not support the the use of TABLE variable. Therefore a workaround is to use Temporary Tables in SqlDW. If we run the below statement in Synapse SQL then it willa throw syntax error as below:

/*Standard use on TABLE Variable*/
DECLARE @tblExclude TABLE ([ColumnName] sysname NOT NULL);

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 1, column: 21: Incorrect syntax near 'T

Alternative solution to achive the same goal as Table Variable in SqlDW / Synapse SQL is to use temp tables as below:

IF OBJECT_ID('tempdb..#tblExclude') IS NOT NULL
	DROP TABLE #tblExclude

CREATE TABLE #tblExclude ([ColumnName] SYSNAME NOT NULL)

Leave a Reply