Finding the item count in stored procedure

0
votes

I want to find the number of nulls for a col_name. I am using the code below in SSMS:

DECLARE @NumNull INT = N'SELECT COUNT(*) FROM ' + @WhichTable + N' WHERE col_name IS NULL'

I have declared @WhichTable as NVARCHAR(MAX) at the beginning of my procedure.

I am getting this error message:

Msg 245, Level 16, State 1, Procedure sp_FILL_CHAIN_NAMES, Line 22
Conversion failed when converting the nvarchar value 'SELECT COUNT(*) FROM UNION_ALL WHERE col_name IS NULL' to data type int.

I am not sure how to rewrite this SQL code? I tried exec(), and even SQL but it does not seem to be working!

2
sql-serverstored-procedures
Possible duplicate of SQL Server dynamic queries - Juan Carlos Oropeza
All your tables have a column called col_name? - Caius Jard
Clearly it's a string. Why do you declare your variable as an INT? - Eric
Maybe Google how to do dynamic SQL. - Eric

2 Answers

2
votes

You trying to store whole SELECT statement in an int variable, not the result of your select.

You need to:

  1. Store your SQL in NVARCHAR variable.
  2. Use sp_executesql to execute it.

Good example how to do it is available there: https://support.microsoft.com/en-us/help/262499/how-to-specify-output-parameters-when-you-use-the-sp-executesql-stored

1
votes

You need to use sp_executesql to assign result from a dynamic sql

DECLARE @WhichTable nvarchar(max)= 'Categories'
declare @NumNull int 
DECLARE @sql nvarchar(max)

Set @sql = 'SELECT @NumNull = COUNT(*) FROM ' + @WhichTable + ' WHERE CategoryId IS NOT NULL';
execute sp_executesql 
    @sql, 
    N'@NumNull int OUTPUT', 
    @NumNull = @NumNull output;
select @NumNull;