SQL Azure Data warehouse Dynamic SQL Select query

Question

In Azure SQL Data Warehouse stored procedure I try to form Dynamic SQL and perform select query,I unable to fetch the resutSet Below is code Snippet:

DECLARE @sql nvarchar(400)
DECLARE @cnt int
BEGIN
---Some Business Logic---
SELECT @sql = N'select @cnt = count(*) from  '+quotename(@src_TableName)+' where warn_remarks  like ''%'+ @condition +'%''';
SET @parameter = N'@cnt int OUTPUT'
EXECUTE sp_executesql @sql,@parameter,@cnt = @cnt OUTPUT        
END

The Error it is showing is "Incorrect syntax near '='",In the select query when assigning count(*) to a variable it is giving error.But the same logic is working fine in Azure SQL Database.Kindly help to solve this.

Answer 1

You'll need to use SET instead, because you can't set variables using SELECT in SQL DW or PDW.

DECLARE @sql nvarchar(400)
DECLARE @cnt int
BEGIN
---Some Business Logic---
SET @sql = N'SET @cnt = (select count(*) from  '+quotename(@src_TableName)+' where warn_remarks  like ''%'+ @condition +'%'')'; --replaced
SET @parameter = N'@cnt int OUTPUT'
EXECUTE sp_executesql @sql,@parameter,@cnt = @cnt OUTPUT        
END

Answer 2

Select @var is not supported in Azure SQL data warehouse. https://msdn.microsoft.com/en-us/library/ms187330.aspx

You can find workarounds https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-develop-variable-assignment/