Click here to Skip to main content
15,898,134 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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!
Posted

1 solution

You could wrap the case operations up in a function, which would mean changing the code in one place. Also making the whole thing more readiable.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900