begin end 调试sqlServer函数用，直接在自定义函数中是不能使用 exec ( ) 动态执行执行用的，exec ( )的实际有效运用还未知
BEGINDECLARE @paraOPFLAG NUMERIC=2;DECLARE @paraTJH000 NUMERIC=659;DECLARE @paraBMBH00 NUMERIC=10;DECLARE @VSTR000 varchar(1000); DECLARE @VSQLSTR varchar(1000);DECLARE @VCXTJ00 varchar(500);DECLARE @VBMMC01 varchar(30);DECLARE @VKSLX00 varchar(50); DECLARE @VCSKSBH char(100); DECLARE @VCJKSBH char(100); DECLARE @VYYID00 char(100); DECLARE @VFJWC00 char(100); DECLARE @VKSSH00 char(100); DECLARE @VSFXMMC varchar(50); DECLARE @VFBBH00 NUMERIC(5); declare @dynamicSQL VARCHAR(1000);SELECT @VBMMC01 = BMMC00,@VKSLX00 = KSLX00 FROM BM_BMBM00 WHERE BMBH00 = @paraBMBH00;SET @VSTR000 = null;select @VYYID00 = YYID00, @VFBBH00 = FBBH00 from PE_YWSJ_TJZ000 where TJH000 =@paraTJH000;if @paraOPFLAG = 2 BEGINselect @VKSLX00 = KSLX00 from BM_BMBM00 where BMBH00 =@paraBMBH00;select @VCSKSBH = dbo.SF_PE_GetXTCS('PE_SYCSKSBM', '', @VYYID00) ;if CHARINDEX(','+cast(@paraBMBH00 as varchar )+',' ,','+@VCSKSBH+',') > 0 BEGIN SET @VCXTJ00 = 'and JCKSID in ('+@VCSKSBH+') '; endelse BEGINselect @VCJKSBH = dbo.SF_PE_GetXTCS('PE_CJKS', '', @VYYID00) ;if @VKSLX00 = 'PATHOLOGY' and @VCJKSBH is not null beginset @VCXTJ00 = 'and JCKSID in ('+@paraBMBH00+','+@VCJKSBH+')' endelse BEGINSET @VCXTJ00 = 'and JCKSID in ('+@VCSKSBH+') '; endendset @dynamicSQL = 'declare CUR_XM cursor for '+'select SFXMMC from SY_PE_YWSJ_TJYW00 A where TJH000 ='+cast(@paraTJH000 as VARCHAR)+'and QJ0000 = 0 and TFNTBZ = 0'+' and dbo.SF_PE_JudgeSF(SFYWID) = 1 '+@VCXTJ00+ ' and not exists (select 1 from SY_PE_YWZD_TJSQXM where SQXMID = A.SQXMID and SFJCXM =0)'exec (@dynamicSQL) open CUR_XM fetch next from CUR_XM into @VSFXMMCwhile @@FETCH_STATUS=0beginif @VSTR000 is null SET @VSTR000 = @VSFXMMC; else SET @VSTR000 = @VSTR000+' + '+@VSFXMMC;fetch next from CUR_XM into @VSFXMMCENDclose CUR_XMDEALLOCATE CUR_XMbegin select @VSTR000 as t endendEND
另外一种方式：使用 EXEC sp_executesql
1、将上的 declare @dynamicSQL VARCHAR(1000) 定义为
declare @dynamicSQL NVARCHAR(1000);
2、将上的 exec (@dynamicSQL) 改为
EXEC sp_executesql @dynamicSQL
可以达到同样结果
如果觉得《SqlServer中使用动态sql语句》对你有帮助，请点赞、收藏，并留下你的观点哦！
