SQL Azure Data warehouse Dynamic SQL Select query

4
votes

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.

2
azure-sql-databaseazure-sqldw

2 Answers

8
votes

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