游标是否可以对每行100个不同的列执行相同的计算? [英] Cursor Possibility to perform same calculations on 100 different columns per row?

问题描述

大家好
我有一个具有100列的sp，在其中我执行相同的CASE并对每个函数进行函数调用.
目前，我对每列重复相同的语句100次，但我认为我无法对其游标b/c进行游标操作，而不能对我要遍历的数据行进行游标操作.另外，我没有2008年，过去，游标的速度很慢.
有没有人建议我可以遍历各列并获得一次执行的代码，因此我不必对代码中的所有100个部分进行更改?另外，此记录集可能非常大，因此性能非常重要.
这是我的代码现在的样子...

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

我知道它看起来很混乱，但这是在列1-100上执行的代码
谢谢！

I know it looks confusing but this is the code that is performed on columns 1-100
Thanks!

推荐答案

您可以将case操作包装在一个函数中，这意味着在一处更改代码.也使整个内容更易于阅读.

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.

这篇关于游标是否可以对每行100个不同的列执行相同的计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！