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

qvtsj1bj  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(446)

代码的目标是动态运行查询,如果列中没有包含数据的行,则返回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。

dvtswwa3

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

相关问题