June 23, 2011 at 10:05 am
Hi All
I have a sp that has 100 columns where I perform the same CASE and function call on each of them.
Currently I duplicating the same statements for each column 100 times and didn't think I could do a cursor b/c it's columns not rows of data that I'm iterating through. Also, I don't have 2008 and in the past, Cursors were dog slow.
Does anyone have any suggestions where I could just iterate through the columns and have the code that it performs once so I don't have to make changes to all 100 pieces in the code? Also, this record set can be quite large so performance is important.
Here is what my code looks like right now...
[LN 00005_RcvCo] = CASE
WHEN LEFT([LN 00005_RcvCo], 1) = 1 THEN
'G_' + CONVERT(VARCHAR, RIGHT([LN 00005_RcvCo], LEN([LN 00005_RcvCo])-2) )
WHEN ( [LN 00005_RcvCo] IS NOT NULL OR (IS_LN <= '00005'OR IS_LN IN ('09997','09998') ) ) AND (LNEnd >= 5 OR LNEnd >= 9900) THEN
dbo.fn_CalcIsColor( [IS_LN], [Static], [Fatigue], LNStart, LNEnd
, CASE WHEN IS_LN IS NULL THEN NULL ELSE 1 END
, [LN 00005_RcvCo], [LN 00005 CtrlStnFlag], IsSeqCo, '00005')
WHEN ([LN 00005_RcvCo] IS NULL AND ('5' BETWEEN LNStart AND LNEnd ) ) THEN
'Z_'
ELSE 'W_' END
I know it looks confusing but this is the code that is performed on columns 1-100
June 23, 2011 at 10:36 am
While your desire not to have to make identical changes in 100 places is commendable, there isn't any sort of a "column loop" in T-SQL. You might consider writing a dynamic SQL query to replicate your CASE once for each of the column names in the table. At least that way when you make a change it can be regenerated consistently for all the columns.
It seems to me that you would be better served in this case by a schema that only has one of these columns per row. You might also consider UNPIVOT'ing the columns into rows that could all be run through a single CASE expression, then PIVOT'ing them back into columns for your result. I would expect this approach to run slower than the first suggestion though.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 23, 2011 at 10:48 am
Here's what I would do:
Step 1: extract the columns belonging together (e.g. [LN 00005_RcvCo] and [LN 00005 CtrlStnFlag]) into a separate table with just the primary key of the original table and three columns like LN_number, RcvCo and CtrlStnFlag. This most probably will make the the table more normalized and will prevent to change the code if you have to add another LN number.
Step 2: analyze the function dbo.fn_CalcIsColor(). If there's a reference to another table nd not just a comparison or calculation purely based on the parameters provided to the function: check if it can be change to an in-line table valued function being referenced using CROSS APPLY.
Step 3: apply the case statement to the normalized table (might need a join to the remaining columns of the original table).
Step 4: pivot the data back in the denormalized format (if needed) using the CrossTab method.
June 23, 2011 at 11:06 am
Thanks!
Lutz -- I will try what you suggested. The fn_CalcIsColor() is just a bunch of calculations so that would work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply