通过索引而不是SQL Server存储过程中的名称访问表的列 [英] Access columns of a table by index instead of name in SQL Server stored procedure

问题描述

在SQL Server中，是否可以通过存储过程中的列索引访问列?

Is there a way to access columns by their index within a stored procedure in SQL Server?

目的是计算许多列.我正在阅读有关游标的信息，但我不知道如何应用游标.

The purpose is to compute lots of columns. I was reading about cursors, but I do not know how to apply them.

让我解释一下我的问题:

Let me explain my problem:

我有这样的一行:

field_1 field_2 field_3 field_4 ...field_d  Sfield_1 Sfield_2 Sfield_3...Sfield_n
1       2       3       4          d        10       20       30         n

我需要计算类似(field_1*field1) - (Sfield_1* Sfiled_1) / more...

因此，结果将被存储在表列中d次.

So the result is stored in a table column d times.

所以结果是一个d column * d row表.

由于列数是可变的，因此我正在考虑制作动态SQL，将字符串中的列名获取并拆分所需的列名，但是这种方法使问题变得更加棘手.我以为按索引获取列号可以使工作更轻松.

As the number of columns is variable, I was considering making dynamic SQL, getting the names of columns in a string and splitting the ones I need, but this approach makes the problem harder. I thought getting the column number by index could make life easier.

推荐答案

首先，如 OMG小马所述，您不能按其顺序位置引用列.这不是意外.无论是DDL还是DML，都没有为动态模式构建SQL规范.

First, as OMG Ponies stated, you cannot reference columns by their ordinal position. This is not an accident. The SQL specification is not built for dynamic schema either in DDL or DML.

鉴于此，我不得不想知道为什么要像您那样对数据进行结构化.当您尝试提取信息时，架构和问题域之间不匹配的迹象会重新出现.如果编写查询非常繁琐，则表明该架构无法正确地为其设计域建模.

Given that, I have to wonder why you have your data structured as you do. A sign of a mismatch between schema and the problem domain rears itself when you try to extract information. When queries are incredibly cumbersome to write, it is an indication that the schema does not properly model the domain for which it was designed.

但是，鉴于您所告诉我们的内容，可能的替代解决方案如下:(我假设field_1*field1的意思是field_1 * field_1或field_1的平方或Power( field_1, 2 ))

However, be that as it may, given what you have told us, an alternate solution would be something like the following: (I'm assuming that field_1*field1 was meant to be field_1 * field_1 or field_1 squared or Power( field_1, 2 ) )

Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
Union All Select 2, field_2, Sfield_2, Sfiled_2
...
Union All Select n, field_n, Sfield_n, Sfiled_n

现在您的查询如下:

With Inputs As
    (
    Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
    Union All Select 2, field_2, Sfield_2, Sfiled_2
    ....
    )
    ,  Results As
    (
    Select Case
            When Sequence = 1 Then Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            Else 1 / Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            End
            As Result
    From Inputs
    )
Select Exp( Sum( Log( Result ) ) )
From Results

这篇关于通过索引而不是SQL Server存储过程中的名称访问表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！