1

We are working on DW to Synapse migration. In that I am migrating an Stored Procedure which having @@error, @@rowcount variables.

@@ Variables not working in Synapse Dedicated SQL pool (@@error, @@rowcount, etc.,)

Any other ways to replace these parameters ??

Sankar M
  • 71
  • 6
  • What does "not working" mean? [@@ROWCOUNT](https://learn.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver16) doesn't list Synapse in its "Applies To", but [@@ERROR](https://learn.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver16) does. – Thom A Mar 29 '23 at 14:12
  • 2
    `@@error` -> embrace `TRY/CATCH` anyway, it is much more powerful and robust. – Aaron Bertrand Mar 29 '23 at 14:12
  • Please don't post "not working". It means nothing. Please post the actual error – Nick.Mc Mar 30 '23 at 10:27

1 Answers1

0

AS @Thom A said @@rowcount will not support by Azure Synapse. But @@error will support in Azure synapse. Example: Sample Code:

UPDATE Product.dbo
SET Name = 'Good'
WHERE id = 1
IF  @@ERROR = 547
BEGIN
    PRINT  N'A check constraint violation occurred.';
END

select * from Product.dbo

Output:

enter image description here

Alternative way of @@row count :

Run a query with query label using below code:

UPDATE Product.dbo
SET Name = 'Sugar'
OPTION (LABEL = 'QUERYID1');

Output:

enter image description here

Run below query to get the row count:

DECLARE @row_count int;
SELECT  top  1 @row_count = row_count
FROM sys.dm_pdw_request_steps s, sys.dm_pdw_exec_requests r
Where r.request_id = s.request_id
and row_count > -1
and r.[label] = 'QueryID1'
order  by r.[end_time] desc;
print @row_count

Output:

enter image description here

For more information you can refer this document.

Bhavani
  • 1,725
  • 1
  • 3
  • 6