Access table columns by index instead of name in SQL Server stored procedure

Question

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

The goal is to compute multiple columns. I read about cursors, but I don't know how to use them.

Let me explain my problem:

I have a line like:

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 

I need to compute something like (field_1*field1) - (Sfield_1* Sfiled_1) / more...

Thus, the result is stored in the table column d times.

So, the result is a table d column * d row .

Since the number of columns is variable, I considered creating dynamic SQL, getting the column names in a row, and separating the ones I need, but this approach complicates the task. I thought getting a column number by index could make life easier.

Answer 1

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

Given this, I need to wonder why you have your data structured in the same way as you. A mismatch between the schema and the problem domain occurs when you try to extract information. When queries are incredibly cumbersome to write, this indicates that the schema does not correctly model the domain for which it was designed.

However, be that as it may, given what you told us, the alternative solution would look something like this: (I assume that field_1*field1 should have been 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 

Your request now looks like this:

 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 

Answer 2

No, you cannot use ordinal (numeric) position in a SELECT .

Only in the ORDER BY can you use ordinal position because it is based on the column (s) specified in the SELECT .

Answer 3

It may not be the most elegant or efficient, but it works. I use it to create a new table for faster matching between the data that I need to parse through all the columns / rows.

 DECLARE @sqlCommand varchar(1000) DECLARE @columnNames TABLE (colName varchar(64), colIndex int) DECLARE @TableName varchar(64) = 'YOURTABLE' --Table Name DECLARE @rowNumber int = 2 -- y axis DECLARE @colNumber int = 24 -- x axis DECLARE @myColumnToOrderBy varchar(64) = 'ID' --use primary key --Store column names in a temp table INSERT INTO @columnNames (colName, colIndex) SELECT COL.name AS ColumnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM sys.tables AS TAB INNER JOIN sys.columns AS COL ON COL.object_id = TAB.object_id WHERE TAB.name = @TableName ORDER BY COL.column_id; DECLARE @colName varchar(64) SELECT @colName = colName FROM @columnNames WHERE colIndex = @colNumber --Create Dynamic Query to retrieve the x,y coordinates from table SET @sqlCommand = 'SELECT ' + @colName + ' FROM (SELECT ' + @colName + ', ROW_NUMBER() OVER (ORDER BY ' + @myColumnToOrderBy+ ') AS RowNum FROM ' + @tableName + ') t2 WHERE RowNum = ' + CAST(@rowNumber AS varchar(5)) EXEC(@sqlCommand)