时间戳

首页 > 解决方案 > SQL Server：对 IN 语句使用字符串变量

sql - SQL Server：对 IN 语句使用字符串变量

问题描述

我创建了一个存储过程，将大字符串作为单个变量发送。我想在 IN 语句中使用该字符串进行查询。但是，当我尝试时，我得到 NULL 结果。

ALTER PROCEDURE [dbo].[Validate_Orders] 
    @failed VARCHAR(4099)
AS
    DECLARE @numFailed INT,
            @valFailed DECIMAL(19, 2)

    SET @numFailed = 0
    SET @valFailed = 0

    SET @numFailed = LEN(@failed) - LEN( REPLACE(@failed, ',', '') ) + 1
    PRINT 'Failed List: ' + @failed

    SELECT @valFailed = SUM(Quantity * Price) 
    FROM Orders 
    WHERE OrdNum IN (@failed)

    PRINT 'Number of Sales Orders that Failed Import: ' + CAST(@numFailed as varchar (10) )
    PRINT 'Value of Sales Orders that Failed Import: ' + CONVERT( VARCHAR(64), FORMAT( CASE WHEN @valFailed >= 0 THEN @valFailed ELSE 0 END, 'C') )
GO


EXEC [Validate_OpenSalesOrders] @failed = '''01D89545'',''01D95600'',''01D98436'',''01D98926'',''01D99704'',''01E00297'',''01E00428'',''01E00637'',''01E00787'',''01E01445'',''01E03713'',''01E04232'',''01E04652'',''01E05283'',''01E05304'',''01E05746'',''01E05754'',''01E05824'',''01E05885'',''01E07032'',''01E07103'',''01E07205'',''01E07297'',''01E07665'',''01E07917'',''02A84220'',''03A78284'',''04AA0825'',''04AA0933'',''04AA1134'',''04AA2612'',''04AA2892'',''05A88467'',''05A92716'',''05A93193'',''06A68861'',''07AK9828'',''07AL4784'',''07AL4974'',''07AL9240'',''07AM1817'',''07AM3351'',''07AM4880''...

当它打印失败列表时，它会打印我期望的内容：

Failed List: '01D89545','01D95600','01D98436','01D98926','01D99704','01E00297','01E00428','01E00637','01E00787','01E01445','01E03713','01E04232','01E04652','01E05283','01E05304','01E05746','01E05754','01E05824','01E05885','01E07032','01E07103','01E07205','01E07297','01E07665','01E07917','02A84220','03A78284','04AA0825','04AA0933','04AA1134','04AA2612','04AA2892','05A88467','05A92716','05A93193','06A68861','07AK9828','07AL4784','07AL4974','07AL9240','07AM1817','07AM3351','07AM4880','07AM5526','07AM5774','07AM5845','07AM6988','07AM8264','07AM8529','07AM8768','07AN0661','07AN2003','07AN2318','07AN3297','07AN4190','07AN4490','07AN5141','07AN5150','07AN5339','07AN5740','07AN5843','07AN6584','07AN6628','07AN7088','07AN7129','07AN7582'...

我知道是绳子把它扔掉了，但我不知道如何修复它。

标签： sqlsql-serverstored-proceduressql-server-2014

解决方案

如果您可以接受针对您的过程的 SQL 注入风险，这是一个可能的解决方案：

CREATE TABLE #t(V MONEY);
DECLARE @SQL VARCHAR(MAX) = 'SELECT SUM(Quantity * Price) FROM Orders WHERE OrdNum IN (' + @failed + ')';
PRINT @SQL

INSERT INTO #t(V) EXEC(@SQL);
SELECT @valFailed = V FROM #t;

推荐阅读