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.

CodePudding user response：

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;

CodePudding user response：

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;