SQL2005以上版本怎樣實現一個比sp_executesql支持 變量傳參功能更強大的存儲過程

  1 use master
  2 go
  3 if object_id('Sp_ExecSQL') is not null
  4     drop proc Sp_ExecSQL
  5 go
  6 /******************************************************************************************************************************************************
  7 注:日期類型要準確性，用字符串傳入或字符串輸出(同sp_executesql同樣存在的問題)
  8 
  9 以10參數為例，寫一下實現方法
 10 
 11 作者：中國風(Roy)
 12 
 13 日期:2008.06.19
 14 ******************************************************************************************************************************************************/
 15 create proc Sp_ExecSQL(
 16                 @s nvarchar(4000)=N'',
 17                 @input nvarchar(1000)='',
 18                 @Parameter1 nvarchar(4000)=null output,
 19                 @Parameter2 nvarchar(4000)=null output,
 20                 @Parameter3 nvarchar(4000)=null output,
 21                 @Parameter4 nvarchar(4000)=null output,
 22                 @Parameter5 nvarchar(4000)=null output,
 23                 @Parameter6 nvarchar(4000)=null output,
 24                 @Parameter7 nvarchar(4000)=null output,
 25                 @Parameter8 nvarchar(4000)=null output,
 26                 @Parameter9 nvarchar(4000)=null output,
 27                 @Parameter10 nvarchar(4000)=null output)
 28 as
 29 set nocount on;
 30 begin try
 31 begin tran
 32 declare @dec nvarchar(4000),@set nvarchar(4000),@update nvarchar(4000),@error nvarchar(200)
 33 select @input=replace(replace(@input,nchar(13)+nchar(10),nchar(32)),nchar(9),nchar(32))
 34 
 35 while charindex(nchar(32)+nchar(32),@input)>0
 36     select @input=replace(@input,nchar(32)+nchar(32),nchar(32))
 37 while charindex(nchar(32)+'@',@input)>0
 38     select @input=replace(@input,nchar(32)+'@','@')
 39 while charindex('@'+nchar(32),@input)>0
 40     select @input=replace(@input,'@'+nchar(32),'@')
 41 
 42 if isnull(@s,'')!>''
 43     return
 44 
 45 if object_id('tempdb..#Parameter') is not null
 46     drop table #Parameter
 47 select 
 48     Row=row_number()over(order by t2.row),
 49     Parameter=left(ltrim(t2.Col),charindex(nchar(32),ltrim(t2.Col)+',@')-1),
 50     ParaType=rtrim(stuff(ltrim(t2.Col),1,charindex(nchar(32),ltrim(t2.Col)),'')),
 51     [Value]=cast(null as nvarchar(4000)),
 52     [Flag]=cast(null as bit),
 53     [IsChar]=cast(null as bit)
 54 into #Parameter
 55 from 
 56     (select convert(xml,'<root><v>'+replace(@input,',@','</v><v>@')+'</v></root>'))T(c)
 57 outer apply
 58     (select row=1,Col=N.v.value('.','nvarchar(100)') from T.c.nodes('/root/v')N(v))T2
 59 
 60 
 61 update #Parameter set ParaType=case when charindex(reverse(N'output'),reverse(ParaType))=1 then rtrim(left(ParaType,len(ParaType)-6))
 62                                     when charindex(reverse(N'out'),reverse(ParaType))=1 then rtrim(left(ParaType,len(ParaType)-3))
 63                                     else  ParaType end
 64 
 65 update a
 66 set [IsChar]=b.[IsChar],[Flag]=case when b.Name is null or (b.ID in(106,108) and patindex('%[(]%',replace(ParaType,b.Name,''))>patindex('%[0-9,)]%',replace(ParaType,b.Name,'')))
 67                                     then 1 when  b.[IsChar]=0 and b.ID not in(106,108) and ParaType<>b.Name
 68                                     then 1  else 0 end
 69 from 
 70     #Parameter a
 71 outer apply
 72     (select top 1 ID=user_type_id,Name,case when precision>0 and user_type_id not in(58,61) then 0 else 1 end [IsChar] from sys.types where  charindex(Name,rtrim(a.ParaType))>0 order by len(Name) desc)b
 73 
 74 if exists(select 1 from #Parameter where [Flag]=1)
 75     begin
 76         set @error=N'檢查格式是否正確'
 77         raiserror 50001 N'#'
 78     end
 79 
 80 
 81 update #Parameter 
 82 set [Value]=case row when 1 then @Parameter1 when 2 then @Parameter2 when 3 then @Parameter3 when 4 then @Parameter4 
 83             when 5 then @Parameter5 when 6 then @Parameter6 when 7 then @Parameter7 when 8 then @Parameter8 
 84             when 9 then @Parameter9 when 10 then @Parameter10 end
 85 
 86 select @dec='',@set='',@update=''
 87 
 88 select 
 89     @dec=@dec+case when row=1 then 'Declare ' else ' ,' end +Parameter+' '+ParaType,
 90     @set=@set+case when row=1 then 'select ' else ' ,' end +Parameter+'='
 91         +case when [IsChar]=1 then isnull(quotename([Value],''''),'Null') else isnull(rtrim([Value]),'Null') end,    
 92     @update=@update+case when row=1 then 'Update #Parameter set [Value]= case Row when ' else ' when ' end+rtrim(Row)+' then convert(nvarchar(4000),'+Parameter+')'
 93 from 
 94     #Parameter
 95 order by row asc
 96 
 97 exec(@dec+' '+@set+' '+@s+' '+@update+' end')
 98 
 99 set @Parameter1= (select [Value] from  #Parameter where row=1)
 set @Parameter2= (select [Value] from  #Parameter where row=2)
 set @Parameter3= (select [Value] from  #Parameter where row=3)
 set @Parameter4= (select [Value] from  #Parameter where row=4)
 set @Parameter5= (select [Value] from  #Parameter where row=5)
 set @Parameter6= (select [Value] from  #Parameter where row=6)
 set @Parameter7= (select [Value] from  #Parameter where row=7)
 set @Parameter8= (select [Value] from  #Parameter where row=8)
 set @Parameter9= (select [Value] from  #Parameter where row=9)
 set @Parameter10=(select [Value] from  #Parameter where row=10)
 
 commit tran
 end try
 begin catch
     set @error=isnull(@error,ERROR_MESSAGE())
     rollback tran
     raiserror 50001 @error
 end catch
 
 go
 
 
 declare @i int,@Name sysname,@j decimal(18,5)
 select @i=100
 exec sp_execsql N'select @j=count(1) from sysobjects where ID>@char','     @    char int        , @j decimal (18,5)   output    ',@i,@j output
 select @j