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

sql sql-server tsql sql-server-2008 ordinal
edgarmtze picture edgarmtze · Feb 3, 2011 · Viewed 53.2k times · Source

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:

I have a row 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...

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

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

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.

Answer

OMG Ponies picture OMG Ponies · Feb 3, 2011

No, you can not use the ordinal (numeric) position in the SELECT clause.

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More