Updating table column values ​​from a configuration table using an SQL subquery or dynamic query

Question

I have two tables in my SQL Server

Table no. one

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 the #Data table varInvalidRemarks , and I need the following 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 did this thing with the FAST FORWARD READ ONLY cursor, but I want to do this with a subquery or a 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 due to a performance issue in my stored procedure.

Is it possible to achieve this with a sub-query?

thanks

Answer 1

This is a dynamic SQL solution. See PRINT for 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

You can make such a stored procedure instead of using the 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,''), '' ) 

Answer 3

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 4

As I said earlier, you can use the 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 #config.varColumnName can be changed at any time, the hard-coded CASE expression will not work. Instead, you can use CROSS APPLY for dynamic 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