Update one table column values from config table using SQL subquery or dynamic query

Question

I have two tables in my SQL Server

Table no. 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

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:

 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.

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

Answer 1

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

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

Answer 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

Answer 3

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

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:

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

Answer 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,''),
           ''
       )