Dynamic Select Query - SQL Server Forums

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)

Dynamic Select Query

Author

Topic

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-14 : 00:46:41

Hi,

how do i create a dynamic select query in which i can specify at start as to how many columns to be selected from one table.
as for example.
consider authors table in pubs DB
case 1)

I want to select first 2 columns

case 11)
I want to select last two columns

I wish to use information_schema.columns.

Thanks in advance

Thanks,
Vivek

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-14 : 02:03:43

This will do what you want(Select first 2 columns)



Use pubs
Declare @s varchar(2000)
set @s=''
Select @s=@s+','+Column_Name from Information_Schema.columns 
where Table_Name='authors' and Ordinal_Position in 
(select top 2 Ordinal_Position from Information_Schema.columns 
where Table_Name='authors'  order by  Ordinal_Position )
set @s =substring(@s,2,len(@s)-1)

Exec('select '+@s+' from authors')

Create a stored procedure based on this having parameters and supply the table,number of columns

Madhivanan

Failing to plan is Planning to fail

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-14 : 02:25:36

ya it works ....
thanks

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources