这是一个拼接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 [email protected]while convert(varchar(8),@stime,112)<convert(varchar(8),@etime,112)begin set @stime=dateadd(DD,1,@stime) set @[email protected]+' 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 [email protected]end--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 [email protected]while convert(varchar(8),@stime,112)<convert(varchar(8),@etime,112)begin set @stime=dateadd(DD,1,@stime) set @[email protected]+' 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 [email protected]end--print @sqlexec (@SQL)--------------执行go/*--调用存储过程--*/ EXEC proc_guiji '2011-06-21','2011-07-21','114'
------解决方案--------------------
- SQL code
--若你的sql沒錯，直接執行不就可以了--print @sqlexec(@sql)