请问如何写一个存储过程用于适应不同参数的来返回数据呢?

Question

想设计一个公用的存储过程,反回最后一行的数据,其中输入参数接受一个表名，一个字段名,输出参数用来返回表的PK,和一个字段名里的内容
谢谢!

Answer 1

create procedure sp_test(@tname varchar(40),@cname varchar(40))
as
begin
    declare @sql varchar(8000),@str varchar(200)
    set @sql=''
    set @str=''
    
    SELECT 
        @str=@str+a.name+','
    FROM 
        syscolumns a
    inner join 
        sysobjects d 
    on 
        a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
    where 
        d.name=@tname
        and
        exists(SELECT 1 
               FROM 
                   sysobjects 
               where 
                   xtype='PK' 
                   and 
                   parent_obj=a.id 
                   and 
                   name in (SELECT 
                                name 
                            FROM 
                                sysindexes 
                            WHERE 
                                indid in(SELECT 
                                             indid 
                                         FROM 
                                             sysindexkeys 
                                         WHERE 
                                             id = a.id AND colid=a.colid)))
                                
    set @sql='select top 1 '+@str+@cname+' from '+@tname+' order by '+left(@str,len(@str)-1)+' desc' 
    
    exec(@sql)
end
go

Answer 2

一个sp_executesql取出pk字段名.
一个exec取出所选列的行集

Answer 3

create procedure sp_test(@tname varchar(40),@cname varchar(40),@pk varchar(40) output)
as
begin
    declare @sql varchar(8000)
    set @sql=''
    set @pk=''
    
    SELECT 
        @pk=@pk+a.name+','
    FROM 
        syscolumns a
    inner join 
        sysobjects d 
    on 
        a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
    where 
        d.name=@tname
        and
        exists(SELECT 1 
               FROM 
                   sysobjects 
               where 
                   xtype='PK' 
                   and 
                   parent_obj=a.id 
                   and 
                   name in (SELECT 
                                name 
                            FROM 
                                sysindexes 
                            WHERE 
                                indid in(SELECT 
                                             indid 
                                         FROM 
                                             sysindexkeys 
                                         WHERE 
                                             id = a.id AND colid=a.colid)))
    
    set @pk=left(@str,len(@str)-1)
                               
    set @sql='select top 1 '+@pk+','+@cname+' from '+@tname+' order by '+@pk+' desc' 
    
    exec(@sql)
    
    return
end
go