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
