Store the value of a query into a SQL Server variable

Question

The objective of the code is to run a query dynamically and return 0 if there are no rows with data present in the columns and to return 1 if there are rows with data in the columns. This is my code for the stored procedure:

ALTER proc [dbo].[usp_ColumnFieldValidator] 
(
    @TblName nvarchar(30),
    @ColumnName nvarchar(30),
    @RetVal bit output
)


as 
begin

    declare @CountOfRowsQuery as nvarchar(300)
    set @CountOfRowsQuery = 'select count('+quotename(@ColumnName)+') from '+quotename(@TblName)+' having count(' +quotename(@ColumnName)+') = nullif(count('+quotename(@ColumnName)+'),0)'
    execute sp_executesql @CountOfRowsQuery 

    select @RetVal = dbo.fn_ColumnValidator(@CountOfRowsQuery)

end

As you can see, a user-defined function is being called to set the value of @RetVal. This is my code for the user-defined function.

ALTER function [dbo].[fn_ColumnValidator]
(
    @NullChecker as nvarchar(max)
)
returns bit

as
begin
    declare @returnVar as bit
    if @NullChecker is null
        set @returnVar = 0 
    else
        set @returnVar = 1
    return @returnVar
end

The output of @RetVal is always 1 and I have attributed this error to @CountOfRowsQuery storing the entire string rather than the value of the query ie: @CountOfRowsQuery = null if the count of rows is zero else, @CountOfRowsQuery = the number of rows present in the column. To make things clearer I am attaching screenshots of the output when I run the program.

1. Output of a table that contains rows with data
2. Output of a table that contains no rows with no data

As you can see in list item.2, the sp returns null but the function_returned_value is being set to 1 instead of 0.

Answer 1

The objective of the code is to run a query dynamically and return 0 if there are no rows with data present in the columns and to return 1 if there are rows with data in the columns.

Man, if this is not an over-complication I don't know what is.
Here's a much simpler (and more efficient) query that does the work:

SELECT CAST(IIF(EXISTS(
    SELECT 1
    FROM TableName
    WHERE ColumnName IS NOT NULL
), 1, 0) As Bit)

Now, to change that to a procedure using dynamic SQL in a way that will not expose you to SQL Injection threats you can do this:

ALTER PROCEDURE [dbo].[usp_ColumnFieldValidator] 
(
    @TblName sysname, 
    @ColumnName sysname,
    @RetVal bit output
)


AS
BEGIN

    IF NOT EXISTS(
        SELECT 1
        FROM Information_Schema.Columns
        WHERE Table_Name = @TblName
        AND Column_Name = @ColumnName
    ) 
        RETURN;

    DECLARE @Sql nvarchar(1000) =

    N'SELECT @RetVal = CAST(IIF(EXISTS(
        SELECT 1
        FROM '+ QUOTENAME(@TblName) + N'
        WHERE '+ QUOTENAME(@ColumnName) + N' IS NOT NULL
    ), 1, 0) As Bit)'

    EXEC sp_executesql @Sql, N'@RetVal bit output', @RetVal OUTPUT;

END

Key notes:

1. I've changed the @TblName and @ColumnName variables to data type sysname instead of your original nvarchar(30) - since that is the data type SQL Server use internally to store identifiers.

2. Since identifiers can't be parameterized, I've white-listed them.

3. I'm using sp_executeSql to get back the value of the dynamic query directly into my output parameter.

For more tips and tricks on dynamic SQL, you can read my blog post entitled The do’s and don’ts of dynamic SQL for SQL Server