Reference columns by position in stored proc

[GHolden]

I would like to know if it's possible to make a stored procedue that can reference fields by position rather than by name.

I have a number of tables to update/insert into etc. All are of the format KeyID, Description. But the actual field names are different. I would like to use one stored proc for all of them.

I know how to pass the table name to the procedure but I want to avoid passing the field names as well if possible.

eg.

Declare @table varchar(20)
Declare @value varchar(10)
Declare @sql varchar(500)

set @sql='SELECT * FROM ' + @table + 'WHERE ' + field(0) + '=''' + @value + ''''

EXEC (@sql)

(The bit in red is the bit I'm looking for)

Hope this makes sense to someone.


There are two ways to write error-free programs; only the third one works.

 

[JamesLean]

You can't do this. The only time you can use ordinals is in the ORDER BY clause:

SELECT col1, col2
FROM table
ORDER BY 1

--James

 

[GHolden]

If anyone is interested I found a way round this...

DECLARE @FIELD VARCHAR(30)
DECLARE @SQL VARCHAR(400)
DECLARE @TABLE VARCHAR(30)
SET @TABLE = 'dbo.tblInstallation'

SET @FIELD = COL_NAME(OBJECT_ID( @TABLE ), 1)

SET @SQL = 'SELECT ' + @FIELD + ' FROM ' + @TABLE

EXEC (@SQL)

There are two ways to write error-free programs; only the third one works.

 

[TheBugSlayer]

Yep. That's the only way to do it.