在存储过程中如何执行动态SQL语句
这是一个拼接SQL语句的存储过程
- SQL code
use kj249go/*--检查存储过程是否存在--*/if exists(select * from sysobjects where name='proc_guiji') drop procedure proc_guijigo/*--创建储过程--*/create procedure proc_guiji@stime datetime='',@etime datetime='',@bh varchar (100)=''asdeclare @sql varchar(8000)set @sql=' select bh as bh,(select name from personinf where bh=P'+convert(varchar(8),@stime,112)+'.bh) as name,ygh as ygh,point as point,(select fzwzh from fzinfo where fzname=point) as fzwzh,rtime as rtime from p'+convert(varchar(8),@stime,112)+' where bh='+@bhwhile convert(varchar(8),@stime,112)<convert(varchar(8),@etime,112)begin set @stime=dateadd(DD,1,@stime) set @sql=@sql+' union all select bh as bh,(select name from personinf where bh=P'+convert(varchar(8),@stime,112)+'.bh) as name,ygh as ygh,point as point,(select fzwzh from fzinfo where fzname=point) as fzwzh,rtime as rtime from p'+convert(varchar(8),@stime,112)+' where bh='+@bhend--print @sqlgo/*--调用存储过程--*/ EXEC proc_guiji '2011-06-21','2011-07-21','114'
如何执行这个动态的SQL语句 并返回结果
[解决办法]
- SQL code
use kj249go/*--检查存储过程是否存在--*/if exists(select * from sysobjects where name='proc_guiji') drop procedure proc_guijigo/*--创建储过程--*/create procedure proc_guiji@stime datetime='',@etime datetime='',@bh varchar (100)=''asdeclare @sql varchar(8000)set @sql=' select bh as bh,(select name from personinf where bh=P'+convert(varchar(8),@stime,112)+'.bh) as name,ygh as ygh,point as point,(select fzwzh from fzinfo where fzname=point) as fzwzh,rtime as rtime from p'+convert(varchar(8),@stime,112)+' where bh='+@bhwhile convert(varchar(8),@stime,112)<convert(varchar(8),@etime,112)begin set @stime=dateadd(DD,1,@stime) set @sql=@sql+' union all select bh as bh,(select name from personinf where bh=P'+convert(varchar(8),@stime,112)+'.bh) as name,ygh as ygh,point as point,(select fzwzh from fzinfo where fzname=point) as fzwzh,rtime as rtime from p'+convert(varchar(8),@stime,112)+' where bh='+@bhend--print @sqlexec (@SQL)--------------执行go/*--调用存储过程--*/ EXEC proc_guiji '2011-06-21','2011-07-21','114'
[解决办法]
- SQL code
--若你的sql，直接行不就可以了--print @sqlexec(@sql)