使用SQL子查询或动态查询从配置表更新一个表列值

I have two tables in my SQL Server

我的SQL Server中有两个表

Table no. 1

表号1

CREATE TABLE #Config
(
  varColumnName VARCHAR(200) ,
  varAliasName VARCHAR(200)
)

INSERT INTO #Config (varColumnName, varAliasName)
VALUES ('Consumed (Strips)', 'decQuantity'),
       ('Closing (Strips)', 'decClosing')

Table no. 2

表号2

CREATE TABLE #Data
(
    decQuantity DECIMAL(18, 2) ,
    decClosing DECIMAL(18, 2) ,
    varInvalidRemarks VARCHAR(MAX)
)

INSERT INTO #Data (decQuantity, decClosing)
VALUES (10.10, 25.00),
       (-15.10, 45.00),
       (5.10, -10.00),
       (-25.10, -10.00)

From these two tables I want to update #Data table's varInvalidRemarks column and I want the follwing output:

从这两个表我想更新#Data表的varInvalidRemarks列,我想要以下输出:

 decQuantity  | decClosing |  varInvalidRemarks
 -------------|------------|--------------------------
       10.10  |     25.00  |    
      -15.10  |     45.00  |  Consumed (Strips) can NOT be negetive
        5.10  |    -10.00  |  Closing (Strips) can NOT be negetive
      -25.10  |    -10.00  |  Consumed (Strips) can not be negetive,Closing(Strips) can not be negetive

I have done this thing with a FAST FORWARD READ ONLY cursor but I want to do it with a sub query or dynamic query.

我用FAST FORWARD READ ONLY游标做了这件事,但我想用子查询或动态查询来做。

DECLARE @varColumnName VARCHAR(200) ,
@varAliasName VARCHAR(200)

DECLARE DisplayColumn CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  C.varColumnName ,
        C.varAliasName
FROM    #Config AS C

OPEN DisplayColumn

FETCH NEXT FROM DisplayColumn INTO @varColumnName, @varAliasName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('
    UPDATE  D
    SET     D.varInvalidRemarks = isnull(D.varInvalidRemarks,'''') +
    '','+@varColumnName +' can not be negative ''
    FROM    #Data AS D
    WHERE   CAST(ISNULL(D.'+@varAliasName+', 0) AS DECIMAL(18, 2)) < 0

    ')

    FETCH NEXT FROM DisplayColumn INTO @varColumnName, @varAliasName

END

CLOSE DisplayColumn
DEALLOCATE DisplayColumn

I want to do this because of performace issue in my stored procedure.

我想这样做是因为我的存储过程中的性能问题。

Is it possible to achieve this thing with sub query?

用子查询可以实现这个功能吗?

Thanks

4 个解决方案

#1

this is a dynamic SQL solution. See the PRINT for the dynamic query

这是一个动态SQL解决方案。请参阅PRINT以获取动态查询

declare @sql    nvarchar(max),
    @col    nvarchar(max)

select  @col    = isnull(@col + 'UNION ALL' + char(13), '')
        + 'SELECT rmk = ''' + c.varColumnName + ' cannot be negative'' WHERE ' + quotename(c.varAliasName) + ' < 0' + char(13)
from    #Config c

select  @sql    = isnull(@sql, '')
        + N'UPDATE D SET varInvalidRemarks = STUFF(V.Remarks, 1, 1, '''')'  + char(13)
        + N'FROM #Data D'                           + char(13)
        + N'OUTER APPLY'                            + char(13)
        + N'('                                  + char(13)
        + N'SELECT '','' + rmk'                         + char(13)
        + N'FROM'                               + char(13)
        + N'('                                  + char(13)
        + @col                                  + char(13)
        + N') V'                                + char(13)
        + N'FOR XML PATH ('''')'                        + char(13)
        + N') V (Remarks)'                          + char(13)
FROM    #Config c

print   @col
print   @sql
exec    sp_executesql @sql

#2

Something like this should work:

像这样的东西应该工作:

UPDATE #Data
SET varInvalidRemarks = 
  CASE
  WHEN decQuantity < 0 AND decClosing < 0
    THEN 'Consumed (Strips) can NOT be negative,Closing(Strips) can not be negative'
  WHEN decQuantity < 0
    THEN 'Consumed (Strips) can NOT be negetive'
  WHEN decClosing < 0
    THEN 'Closing (Strips) can NOT be negetive'
  ELSE NULL
  END

#3

As I've commented previously, you can use a CASE expression to do the UPDATE:

正如我之前评论的那样,您可以使用CASE表达式来执行更新:

UPDATE #Data
    SET varInvalidremarks = STUFF((
        SELECT
            CASE WHEN decQuantity < 0 THEN ',Consumed (Strips) cannot be negative' ELSE '' END +
            CASE WHEN decClosing < 0 THEN ',Closing (Strips) cannot be negative' ELSE '' END 
        ),1, 1, '')

However, if the #config.varColumnName can be modified at any time, the hardcoded CASE expression will not work. Instead, you can use CROSS APPLY to get varColumnName dynamically:

但是,如果可以随时修改#config.varColumnName,则硬编码的CASE表达式将不起作用。相反,您可以使用CROSS APPLY动态获取varColumnName:

UPDATE d
    SET varInvalidremarks = STUFF((
        SELECT
            CASE WHEN d.decQuantity < 0 THEN ',' + c.decQuantity + ' cannot be negative' ELSE '' END +
            CASE WHEN d.decClosing < 0 THEN ',' + c.decClosing +' cannot be negative' ELSE '' END 
        ),1, 1, '')
FROM #Data d
CROSS APPLY(
    SELECT
        decQuantity = MAX(CASE WHEN varAliasName = 'decQuantity' THEN varColumnName END),
        decClosing  = MAX(CASE WHEN varAliasName = 'decClosing' THEN varColumnName END)
    FROM #Config
) c

#4

You could make such stored procedure instead of using cursor.

你可以制作这样的存储过程而不是使用游标。

UPDATE #Data
SET varInvalidRemarks = ISNULL(
           STUFF((SELECT CASE WHEN decQuantity < 0 THEN ',Consumed (Strips) cannot be negative' ELSE '' END
                          + CASE WHEN decClosing < 0 THEN ',Closing (Strips) cannot be negative' ELSE '' END
               ),1,1,''),
           ''
       )

#1