sel tablename, 'comma-separated-list-of-columnnumbers'

And now the promised stored procedure:

if object_id ('sel') > 0
    drop procedure sel
go

create procedure dbo.sel (
    @tablename sysname,
    @mode varchar(255),
    @order int = 1,
    @where varchar(1000) = ''
) as
begin
    -- Bernd Kriszio 2010-05-07
    declare @cmd varchar(8000)
    declare @columnquery varchar(8000)
    declare @cnt integer
    declare @t_Name sysname
    declare @column_Name sysname
    declare @type integer
    Declare @anz integer

    Declare @table_names table(bez sysname)

    insert into @table_names select name from sysobjects where type in ('U', 'V') and name like (@tablename + '%')

    select @anz = count(1) from  @table_names
    if (@anz) = 1
    begin
        set @t_Name = (select bez from @table_names)
        Print @t_Name
    end
    else if (@anz > 0)
    begin
        Select 'ambigious '+ bez from @table_names
        return
     end

    if substring(@mode,1,1) = '*'
    begin
        set @cmd = 'Select * from ' + @t_Name + ' ' + @where + ' order by ' + str(@order)
        print @cmd 
        exec (@cmd)
    end

    else
    begin
        set @cmd = ''
        if (Select count(*) from splitColumnNumberList(@mode, ',')) = 1
        begin
            set @cnt = @mode
            declare column_cursor cursor for
                select c.name from syscolumns c join sysobjects o on c.id = o.id where o.name = @t_Name and colorder <= @cnt
        end
        else
            declare column_cursor cursor for
                select c.name from syscolumns c
                    join sysobjects o on c.id = o.id
                    join splitColumnNumberList( @mode, ',') on colid = value
                where o.name = @t_Name
                order by lfd
        open column_cursor
        FETCH NEXT From column_cursor into @column_Name
        WHILE @@FETCH_STATUS = 0
        begin
            --Print @column_Name
            set @cmd = case when @cmd  = '' then 'Select ' + @column_Name else @cmd + ', ' + @column_Name end
            FETCH NEXT From column_cursor into @column_Name
        end
        close column_cursor
        deallocate column_cursor
        set @cmd = @cmd + ' from ' + @t_Name + ' ' + @where + ' order by '+ str(@order)
        print @cmd
        exec (@cmd)
    end   


end
go





you do not need to type a full tablename. If the beginning is unique the table (or view) is used. Otherwise a list with the possible names is return.


With the second parameter, you select the columns. If you have a table


create table demo (
   id int,
   value2 varchar(30),
   value3 varchar(30),
   value4 varchar(30),
   value5 varchar(30),
   value6 varchar(30)
)

you can use

sel dem, '1, 6, 3',  3

instead of

Select id, value6, value3 from demo order by 3

Note the order by number is relativ to the selected columns, not to the original table or view.

Have fun playing and extending this.

Bernd