Reference column by ordinal number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reference column by ordinal number?

Author

Topic

lawyer
Starting Member

5 Posts

Posted - 2003-08-06 : 15:45:35

In VB I can reference the column of a table by ordinal number (rather than field name) like this:

ID = rs.fields(0).value

Can the same be done with Transact SQL?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 15:49:02

You can do that in the ORDER BY:

SELECT Column1, Column2
FROM Table1
ORDER BY 1

But I've heard that this functionality will be going away in future versions.

But why would you want to reference it by the ordinal number? Is it because of lazy programming? Wouldn't it be harder for a new developer to read your code if you went this way?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 16:02:28

quote:
Originally posted by tduggan

You can do that in the ORDER BY:

SELECT Column1, Column2
FROM Table1
ORDER BY 1

But I've heard that this functionality will be going away in future versions.

But why would you want to reference it by the ordinal number? Is it because of lazy programming? Wouldn't it be harder for a new developer to read your code if you went this way?

Tara

Nah, s/he's working with arrays where they spin through stuff..very usefule outside of the set processing world.

No ther are no arrays in SQL, but there are ways to mimic it.

Ken Hedersons Book "The Guru's guide to SQL Server, Store Procedures, XML and HTML" devotes an entire chapter to the subject.

But if you really have to communicate to SQL server to do work, let the server do the heavy lifting.

Unless I've missed the mark (again)

Brett

8-)

SELECT POST=NewId()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 16:04:53

quote:
Originally posted by X002548

Nah, s/he's working with arrays where they spin through stuff..very usefule outside of the set processing world.

I meant why would you want to do this in T-SQL.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 16:08:33

lawyer,

why not tell us what you're trying to do...

Brett

8-)

SELECT POST=NewId()

lawyer
Starting Member

5 Posts

Posted - 2003-08-06 : 16:33:17

Unfortunately, the answer (i.e., what am I doing?) is "lazy programming".

I'm working on a site in which I will permit file uploads to different tables (7 to be exact). Rather than create a different stored procedure to insert the file path into each different table, I want to create a single stored procedure to which I can simply pass the table name, the record ID, and the file path information to update any of the 7 tables with the new uploaded file path data.

The SQL statement, I perceive, will look something like this:

UPDATE @TableName SET FilePath = @FilePath WHERE IDtbl = @RecID

The problem is that, though my record ID for each table is ALWAYS the first field, it bears a different name in each table. Therefore, I either have to pass the stored procedure the name of the record ID column OR reference it by ordinal number. I have a very good book on Transact SQL which I have read cover to cover (about a year ago) but I do not recall, nor can I now find, any discussion in the book about referencing the columns by ordinal number (though it is probably there). With the answers provided here, I guess my stored procedure will now look something like this:

UPDATE @TableName SET FilePath = @FilePath WHERE Column1 = @RecID

Thanks for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 16:36:00

Dynamic sql can accomplish what you are trying to do but you are going to lose performance. For performance reasons, you should create separate stored procedures.

You can not reference columns in the column list by the ordinal number in T-SQL.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-06 : 16:40:17

keep it in 1 stored proc, but use a nested-if and have 7 different UPDATE statements.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 16:42:39

Jeff, I didn't think of that. That would definitely work. Would he just check what a variable to see which table to update?


CREATE PROC usp_SomeProc
(@WhichColumn VARCHAR(500))
AS

SET NOCOUNT ON

IF @WhichColumn = 'Column1'
  UPDATE TABLE1
  SET Column1 = 0
ELSE
BEGIN
  IF @WhichColumn = 'Column2'
    UPDATE TABLE2
    SET Column2 = 'SomeValue'
...
END

Tara

lawyer
Starting Member

5 Posts

Posted - 2003-08-06 : 16:43:46

Jeff, I could live with that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources