SQL Server Performance

Select Data from Top N Columns

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Aug 9, 2005.

  1. Madhivanan Moderator


    This is based on the question asked by Dineshasanka in this Topic

    Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want

    Here is the procedure


    Create procedure TopNcolumns (@tableName varchar(100),@n int)
    as
    Declare @s varchar(2000)
    set @s=''
    If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table')
    Begin
    If @n>=0
    Begin
    set rowcount @n
    Select @s=@s+','+ column_name from information_schema.columns
    where table_name=@tablename order by ordinal_position
    Set rowcount 0
    Set @s=substring(@s,2,len(@s)-1)
    Exec('Select '+@s+' from '+@tablename)
    End
    else
    Select 'Negative values are not allowed' as Error
    End
    else
    Select 'Table '+@tableName+' does not exist' as Error



    Madhivanan

    Failing to plan is Planning to fail
  2. ghemant Moderator

    Thanx, its really very nice [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  3. dineshasanka Moderator

    yes it really nice

Share This Page