将查询的值存储到sql server变量中

qvtsj1bj 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(465)

代码的目标是动态运行查询，如果列中没有包含数据的行，则返回0；如果列中有包含数据的行，则返回1。这是我的存储过程代码：

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的值。这是我为用户定义函数编写的代码。

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=column中存在的行数。为了让事情更清楚，我在运行程序时附上了输出的屏幕截图。
包含数据行的表的输出
表的输出，该表不包含没有数据的行
正如您在列表项2中看到的，sp返回null，但是函数返回的值被设置为1而不是0。

sql sql-server dynamic-sql tsql ado.net

来源：https://stackoverflow.com/questions/61890287/store-the-value-of-a-query-into-a-sql-server-variable

1条答案

按热度按时间

dvtswwa31#

代码的目标是动态运行查询，如果列中没有包含数据的行，则返回0；如果列中有包含数据的行，则返回1。
伙计，如果这不是一个过度的复杂我不知道是什么。
下面是一个更简单（更高效）的查询：

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

现在，要将其更改为使用动态sql的过程，使您不会受到sql注入威胁，可以执行以下操作：

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

主要注意事项：
我改变了主意 @TblName 以及 @ColumnName 变量到数据类型 sysname 而不是你原来的 nvarchar(30) -因为这是sql server内部用来存储标识符的数据类型。
因为标识符不能参数化，所以我将它们列为白名单。
我在用 sp_executeSql 将动态查询的值直接返回到输出参数中。
有关动态sql的更多提示和窍门，您可以阅读我的博客文章，标题为the do's and don'ts of dynamic sql for sql server

赞(0）回复(0）举报 2021-08-09

我来回答

将查询的值存储到sql server变量中

1条答案

相关问题

热门标签

最新问答