Set count to variable

user_11689206 0
Viewed 1045

I would like to assign the number of rows of my table to a variable.

DECLARE @ROW_COUNT nvarchar(1000);
SET @sql_row_count = 'SELECT COUNT(*) FROM ' + @TABLE_NAME;
EXEC sp_executesql @sql_row_count, @ROW_COUNT OUTPUT;

SET @ROW_COUNT = cast(@ROW_COUNT as int);
SELECT @ROW_COUNT;

@ROW_COUNT returns null.

Thank for help.

tsql sql-server sql
2 Answers

Comments on your existing query

  1. @ROW_COUNT should be integer. You don't need to use CAST() if you defined it as integer.
  2. You need to assign @ROW_COUNT to COUNT(*).
  3. use QUOTENAME() on the @TABLE_NAME to avoid sql injection.
  4. define the parameters for sp_executesql.

Modified query as follow

DECLARE @ROW_COUNT INT;
DECLARE @sql_row_count NVARCHAR(MAX)

SET @sql_row_count = 'SELECT @ROW_COUNT = COUNT(*) FROM ' + QUOTENAME(@TABLE_NAME);

-- print out for verification
PRINT @sql_row_count 
EXEC sp_executesql @sql_row_count, N'@ROW_COUNT INT OUTPUT', @ROW_COUNT OUTPUT;

-- SET @ROW_COUNT = cast(@ROW_COUNT as int);
SELECT @ROW_COUNT;
user_1856470

You need to alter the dynamic query by adding a variable that will be assigned the value Count(*) and change the call sp_executesql by adding a description of this variable.

Declare @ROW_COUNT Int;
Declare @TABLE_NAME sysname = 'TableName';
Declare @sql_row_count nVarChar(max);
SET @sql_row_count = 'SELECT @ROW_COUNT=COUNT(*) FROM ' + 
@TABLE_NAME;
EXEC sp_executesql @sql_row_count, N'@ROW_COUNT Int OUTPUT', 
@ROW_COUNT=@ROW_COUNT OUTPUT

SELECT @ROW_COUNT;
user_117698590
Related