Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
johnconstraint
Starting Member
23 Posts |
Posted - 2012-10-26 : 11:43:09
|
I have a table "Table1" which I need to update based on a flag from a config table, "Table_Config". The column names on both the tables are same. The two tables look as shown below.Table1: Primary_Key Col1 Col2 Col3 Col4----------------------------------------------------------------------------ABC123 20120731 14 Microsoft 100.00CDE456 20120731 14 IBM 250.00URYT23 20120731 14 Oracle 150.00 Table_Config:Primary_Key Col1 Col2 Col3 Col4---------------------------------------------------------------------------------UpdateFlag N N Y Y I have an existing stored procedure that updates the columns on "Table1" by accepting the column name as parameter, something likeExec usp_UpdateTable "Col1"Exec usp_UpdateTable "Col2"Exec usp_UpdateTable "Col3"I need to call the above stored procedures only when the flag for a particular column on "table_Config" is "Y". I am planning on implementing this by following the below approach:- Get all column names from "Table1" and stored in a temp table. The table will now have "Col1", "Col2", "Col3", etc as rows.- Use a cursor or a while loop to read through the rows in the temp table. This will now return "Col1", "Col2", "Col3", etc.- For each row, check if the Flag is "Y" by reading "Table_Config", if yes call the stored procedure, Exec usp_UpdateTable <column name>Please let me know if you guys think of a better approach. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-26 : 11:54:31
|
You could do something like shown below. But, a better alternative might be to modify the stored proc to update only if the config_table has a Y for that column. Also, if you do it that way, it may be possible to update all the columns in one call rather than calling the stored proc (and the underlying update) multiple times.DECLARE @config VARCHAR(32);SELECT @config = configColumn FROM table_config WHERE ColumnName = 'Col1';IF (@config = 'Y') EXEC usp_UpdateTable 'Col1'SELECT @config = configColumn FROM table_config WHERE ColumnName = 'Col2';IF (@config = 'Y') EXEC usp_UpdateTable 'Col2' |
|
|
johnconstraint
Starting Member
23 Posts |
Posted - 2012-10-26 : 12:16:23
|
Thanks sunitabeck. The update stored procedure is been existing for a while and being called from different places, so I am being asked to not touch that. I forgot to mention one thing in my original post (sorry about that!) - the "Table1" and "Table_Config" has about 25 columns, thats the reason I thought it was a good idea to use a cursor or a while loop to process through all columns instead of having individual sql statements to check the flag. |
|
|
|
|
|
|
|