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...
SELECT
[LN 00005] = CASE
WHEN LEFT([LN 00005_RcvCo], 1) = 1 THEN
Do some work...
WHEN ( [LN 00005_RcvCo] IS NOT NULL THEN
dbo.fn_CalcIsColor( [LN 00005_RcvCo], [LN 00005 CtrlStnFlag], Column3, "0005")
WHEN ([LN 00005_RcvCo] IS NULL AND (5 BETWEEN Column1 AND Column2) ) THEN
Z_
ELSE W_
END
, [LN 00006] = CASE
WHEN LEFT([LN 00006_RcvCo], 1) = 1 THEN
Do some work...
WHEN ( [LN 00006_RcvCo] IS NOT NULL THEN
dbo.fn_CalcIsColor( [LN 00006_RcvCo], [LN 00006 CtrlStnFlag], Column3, "0006")
WHEN ([LN 00006_RcvCo] IS NULL AND (6 BETWEEN Column1 AND Column2) ) THEN
Z_
ELSE W_
END
FROM TABLE
I know it looks confusing but this is the code that is performed on columns 1-100
Thanks!