I recently had a first contact with the Azure Synapse Analytics environment, and when implementing some SQL queries, I came across a problem when trying to declare a variable to run a query with WHILE.
I am sending below the error messages I am getting. I've read the T-SQL documentation and I'm not understanding what I'm doing wrong. If anyone has gone through the same situation and could shed some light on it, I would be very grateful!
%%sql
DECLARE @Counter INT;
SET @Counter = 0;
WHILE ( @Counter <= 3)
BEGIN
PRINT 'The counter value is = ' CONVERT(VARCHAR,@Counter)
SET @Counter = @Counter 1
END
and the message error:
Error: mismatched input 'DECLARE' expecting {'(', 'ADD', 'ALTER', 'ANALYZE', 'CACHE', 'CLEAR', 'COMMENT', 'COMMIT', 'CREATE', 'DELETE', 'DESC', 'DESCRIBE', 'DFS', 'DROP', 'EXPLAIN', 'EXPORT', 'FROM', 'GRANT', 'IMPORT', 'INSERT', 'LIST', 'LOAD', 'LOCK', 'MAP', 'MERGE', 'MSCK', 'REDUCE', 'REFRESH', 'REPLACE', 'RESET', 'REVOKE', 'ROLLBACK', 'SELECT', 'SET', 'SHOW', 'START', 'TABLE', 'TRUNCATE', 'UNCACHE', 'UNLOCK', 'UPDATE', 'USE', 'VALUES', 'WITH'}(line 2, pos 0)
== SQL ==
DECLARE '@Counter' INT
^^^
CodePudding user response:
If you’re in a notebook, using the sql magic, you are writing SparkSQL against a Spark pool not T-SQL against a dedicated SQL pool.
Just write that SQL in Synapse Studio or SSMS or Azure Data Studio when connected to your dedicated or serverless SQL pool.