create proc test @returnval int output as select @returnval=0 godeclare @return int exec test @return output select @returndrop proc test
exec sp_test set @变量='select max(value) from ta'
exec sp_test set @变量=(select max(value) from ta)
--建表: create table tb(id int) insert tb select 1 union all select 3 union all select 100 go --建SP: create proc p @tbname nvarchar(20), @colname nvarchar(20), @retVal int output as declare @sql nvarchar(1000) set @sql='select @retVal=max('+@colname+') from '+@tbname exec sp_executesql @sql,N'@retVal int output',@retVal output go --测试: declare @retVal int exec p 'tb','id',@retVal output select @retVal /* 100 */ drop table tb drop proc p
可以稍微改下,!!codecreate proc pr_test (@tbname varchar(100),@colname varchar(100)) as begin exec('select max'+'(''+@colname+''+')'' as @colname from '+@table) declare @var 类型 set @var=@colname end go 试试看
codecreate proc pr_test (@tbname varchar(100),@colname varchar(100)) as begin exec('select max'+'(''+@colname+''+')' as @colname from '+@table) declare @var 类型 set @var=@colname end go
@returnval int output
as
select @returnval=0
godeclare @return int
exec test @return output
select @returndrop proc test
set @变量='select max(value) from ta'
set @变量=(select max(value) from ta)
create table tb(id int)
insert tb select 1 union all select 3 union all select 100
go
--建SP:
create proc p
@tbname nvarchar(20),
@colname nvarchar(20),
@retVal int output
as
declare @sql nvarchar(1000)
set @sql='select @retVal=max('+@colname+') from '+@tbname
exec sp_executesql @sql,N'@retVal int output',@retVal output
go
--测试:
declare @retVal int
exec p 'tb','id',@retVal output
select @retVal
/*
100
*/
drop table tb
drop proc p
as
begin
exec('select max'+'(''+@colname+''+')'' as @colname from '+@table)
declare @var 类型
set @var=@colname
end
go
试试看
codecreate proc pr_test (@tbname varchar(100),@colname varchar(100))
as
begin
exec('select max'+'(''+@colname+''+')' as @colname from '+@table)
declare @var 类型
set @var=@colname
end
go