SqlServer2012中使用动态sql语句

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

可以达到同样结果