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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With