selecting columns by its index.
-
Hi All,
I am writting a generic query which picks up data from tables which are decided at run time.
So what I want is instead of specifying column name in the query can I specify column index or something like that.
e.g To get data of the first column in the table can I refer it as 0 or 1 in the query.
Please help.
Regards,
brlele
-
Hi,
Use this
select
column_name from information_schema.columns where table_name=@tablename
and ordinal_position=@columnnumber
replace @tablename with u r table and @columnnumber with the column index or number as u wish
-
This isn't 100% - there can be gaps in the ordinal position sequence, which then will offset the ordinal values from the actual columns in the current table definition.
The anser is No. You can't refer to columns by position - it has to be by name. You should carefully consider of it's worth the effort to write such a 'generic' query - it will bring nothing but grief and pain in the end anyway
/Kenneth
-
The only way to use ORDINAL positioning is with ORDER BY and it still is not a good idea...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
No you cannot do
SELECT 1 FROM tblX
You have to specify the column name.
You can however use the previously descirbed to determine the column in an SP or in your app and build a dynamic sql string from that info but keep in mind that you will want to recompile in the SP everytime or your performance will drop quickly.
Can you be descriptive in what your goal is and maybe someone can help you find a solution that meets your need.
-
Each time I read "generic", there's something rotten in the state of Denmark.
Like Antares said, what is your goal? And in the meantime, maybe this helps:
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply