Often there is a requirement that you donot want all columns to be in your select query. For example, if your table has 35 columns and you need to get only 15 columns, then, yo have to specify every column name in your query. Following Stored Procedure helps in fetching top N columns from table. All it requires is table name and number of columns you need in your select statement.
Create procedure SelectTopNColumns (@tableName varchar(100),@numberumber int)
as
Declare @Column varchar(2000)
set @Column=''
If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table')
Begin
If @number>=0
Begin
set rowcount @number
Select @Column=@Column+','+ column_name from information_schema.columns
where table_name=@tablename order by ordinal_position
Set rowcount 0
Set @Column=substring(@Column,2,len(@Column)-1)
Exec('Select '+@Column+' from '+@tablename)
End
else
Select 'Negative values are not allowed' as ErrorFetching
End
else
Select 'Table '+@tableName+' does not exist' as ErrorFetching
|
| Author: Kapil Dhawan 16 Jun 2008 | Member Level: Gold Points : 1 |
Hello,
Nice piece of Code.
Thanks for sharing your knowledge with us.
Regards,
Kapil
|