Azure Synapse Analytics- Alternative for @@ROWCOUNT or ROWCOUNT_BIG – Quick Tip

This quick tip describes how to get Row Count in Azure Synapse Analytics (formerly Azure Data Warehouse).  Azure Data Warehouse doesn’t support @@ROWCOUNT or ROWCOUNT_BIG. The link offers an alternative. This article modified that alternative to return the row count value as an OUTPUT parameter so a variable can be set to the row count value.

First, you need to create this stored procedure in your database:

IF EXISTS ( SELECT * FROM sys.procedures WITH(NOLOCK) WHERE NAME = ‘LastRowCount’ AND SCHEMA_NAME (schema_id) = ‘zdm’ AND type = ‘P’)
DROP PROCEDURE zdm.LastRowCount;
GO
CREATE PROCEDURE zdm.LastRowCount
( @Count int OUTPUT)
AS
BEGIN
SET @Count =
(SELECT
TOP 1 row_count
FROM sys.dm_pdw_request_steps
WHERE row_count >= 0
AND request_id IN (SELECT TOP 1 request_id
FROM sys.dm_pdw_exec_requests
WHERE session_id = SESSION_ID()
AND resource_class IS NOT NULL
ORDER BY end_time DESC) –In
)
END;

This stored procedure returns the affected row count of the SQL statement that was last run. If no rows were affected, it returns NULL.

To use this stored procedure to set a parameter, call it like this:

DECLARE @count int;
EXEC zdm.LastRowCount @count OUTPUT