Set count to variable

Question

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.

Answer 1

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;