哈哈 create procedure DynSql @sql varchar(1024) as begin declare @max int create table #maxtemp (maxint int) insert #maxtemp (maxint) execute(@sql ) select @max=maxint from #maxtemp drop table #maxtemp if @max>1000 .... end
说白了,我就是想有一种不是太复杂的在一个存储过程中处理一个动态执行语句产生的结果集的方法,例如我的@sql可能是‘select max(price) as maxprice from sales' 然后取得maxprice,(用select @max=...)因为后续的处理依赖于动态执行的结果, 当然可以通过临时表来得到,但考虑支效率问题我觉得有太好,我想最好的办法是用openrowset(...)---如果它支持动态sql语句的话,--我想不通为什么M$软不提供这个功能,
使用游标方式,请看下面语句:declare @sql varchar(1024) select @sql='select max(price) as maxprice from sales' -- declare @fn_val int declare @fn_cur_cmd varchar(1024) select @fn_cur_cmd='declare fn_cur cursor for '+@sql exec(@fn_cur_cmd) open fn_cur fetch next from fn_cur into @fn_val close fn_cur deallocate fn_cur -- select @fn_val as fn_val
create procedure DynSql @sql varchar(1024)
as
begin
declare @max int
create table #maxtemp (maxint int)
insert #maxtemp (maxint) execute(@sql )
select @max=maxint from #maxtemp
drop table #maxtemp
if @max>1000 ....
end
然后取得maxprice,(用select @max=...)因为后续的处理依赖于动态执行的结果,
当然可以通过临时表来得到,但考虑支效率问题我觉得有太好,我想最好的办法是用openrowset(...)---如果它支持动态sql语句的话,--我想不通为什么M$软不提供这个功能,
select @sql='select max(price) as maxprice from sales'
--
declare @fn_val int
declare @fn_cur_cmd varchar(1024)
select @fn_cur_cmd='declare fn_cur cursor for '+@sql
exec(@fn_cur_cmd)
open fn_cur
fetch next from fn_cur into @fn_val
close fn_cur
deallocate fn_cur
--
select @fn_val as fn_val
如果在其它情况下用Dbms_sql公用包可以执行动态Sql语句。
create procedure DynSQL @SQL as select * from [@SQL] as TempTable
I've not try.I think you can try it.