Referring to a column using a number

[netcashin]

I would like to be able to refer to a column by a number that refers to the position of the column in the table such 5 for the fifth column. And I would like to do this in a stored procedure. I can to this in VB, but I would like to be able to this in a stored procedure.

Any ideas?

 

[TomSark]

SQL Server 7.0 and beyond:

You could use the COLUMNS information schema view and refer to the ORDINAL_POSITION value to do this...

Tom

 

[netcashin]

Could you help with the syntax to refer to the ordinal_position?

Thanks

 

[TomSark]

Well..

I can take a guess on your needs, but, here's a sample based on your first question... I haven't tested it, but it should work...


use pubs
go

create proc proc_ordinal
(
@table_name sysname,
@posn integer
)
as

declare @column_name sysname,
@exec_str varchar(1000)

select @column_name = column_name
from information_schema.columns
where table_name = @table_name
and ordinal_position = @posn

set @exec_str = 'select ' + @column_name + ' from ' + @table_name

execute (@exec_str)
return 0

 

[TomSark]

Disregard the use pubs statement, I just do that to create the proc on the obus database...

Tom

 

[mikegonsalves]

I too require to refer to columns by their ordinal position, but the prev. soln. cannot be used. Reason for my requirement is that variable length for Dynamic SQL is limited to 128 char, the list of columns exceeds that by a factor of over 3.
So is there any other way of referring to columns by number ?
like maybe select *.1,*.5,*.11 from tbname

 

[tlbroadbent]

mikegonsalves,

Which version of SQL Server are you running? In SQL 7 and 2000 you can create dynamic SQL statements using nvarchar(4000) variables. You can create longer strings by concatenating multiple nvarchar(4000) variables. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.