begin end 调试sqlServer函数用,直接在自定义函数中是不能使用 exec ( ) 动态执行执行用的,exec ( )的实际有效运用还未知
BEGIN
DECLARE @paraOPFLAG NUMERIC=2;
DECLARE @paraTJH000 NUMERIC=2011659;
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 BEGIN
select @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+') '; end
else BEGIN
select @VCJKSBH = dbo.SF_PE_GetXTCS('PE_CJKS', '', @VYYID00) ;
if @VKSLX00 = 'PATHOLOGY' and @VCJKSBH is not null begin
set @VCXTJ00 = 'and JCKSID in ('+@paraBMBH00+','+@VCJKSBH+')' end
else BEGIN
SET @VCXTJ00 = 'and JCKSID in ('+@VCSKSBH+') ';
end
end
set @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 @VSFXMMC
while @@FETCH_STATUS=0
begin
if @VSTR000 is null
SET @VSTR000 = @VSFXMMC;
else
SET @VSTR000 = @VSTR000+' + '+@VSFXMMC;
fetch next from CUR_XM into @VSFXMMC
END
close CUR_XM
DEALLOCATE CUR_XM
begin select @VSTR000 as t end
end
END
另外一种方式:使用 EXEC sp_executesql
1、将上的 declare @dynamicSQL VARCHAR(1000) 定义为
declare @dynamicSQL NVARCHAR(1000);
2、将上的 exec (@dynamicSQL) 改为
EXEC sp_executesql @dynamicSQL
可以达到同样结果