将查询的值存储到SQL Server变量中 [英] Store the value of a query into a SQL Server variable

问题描述

代码的目标是动态运行查询，如果在列中没有数据行，则返回0；在列中有数据行时，返回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. 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

调用了一个用户定义的函数来设置@RetVal的值。这是我的用户定义函数代码。

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

@RetVal的输出始终为1，我将此错误归因于@CountOfRowsQuery，它存储整个字符串而不是查询的值，即：@CountOfRowsQuery = null，如果行数为零，否则@ CountOfRowsQuery =列中存在的行数。为了使事情更清楚，我在运行程序时附加了输出的屏幕截图。

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. 包含包含数据行的表的输出


2. 不包含没有数据行的表的输出

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

如清单2所示，sp返回null，但function_returned_value设置为1而不是0。

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.

推荐答案

该代码的目标是动态运行查询，如果在列中没有数据行，则返回0；如果有数据行，则返回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)

现在，以一种不会暴露您的方式使用动态SQL将其更改为过程对于SQL注入威胁，您可以执行以下操作：

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

主要注意事项：

1. 我更改了 @TblName 和 @ColumnName 数据类型为 sysname 的变量，而不是原始的 nvarchar（30）-因为这是SQL Server的数据类型在内部使用以存储标识符。

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.

由于无法参数化标识符，因此我将其列入白名单。

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

我正在使用 sp_executeSql 将动态查询的值直接返回到我的输出参数中。

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

有关动态SQL的更多技巧，您可以阅读我的博客文章，标题为针对SQL Server的动态SQL的注意事项

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

这篇关于将查询的值存储到SQL Server变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！