怎樣實現一個同sp_executesql支持變量傳參功能的存儲過程

--以05環境實現方法 --2000同樣臨時表實現方法相同!! 动态语句语法同sp_executesql相同 --以sp開頭在master生成，其它庫可能直接調用 use master go if object_id('Sp_ExecSQL') is not null drop proc Sp_ExecSQL go /********************************************************************************************************* 注:日期類型要準確性，用字符串傳入或字符串輸出(同sp_executesql同樣存在的問題) 限制变量不能把'@'需要改为'@a' 在@变量符后要加下其它符号 以10參數為例，寫一下實現方法 作者：中國風(Roy) 日期:2008.06.19 *********************************************************************************************************/ create proc Sp_ExecSQL( @s nvarchar(4000)=N'', @input nvarchar(1000)='', @Parameter1 nvarchar(4000)=null output, @Parameter2 nvarchar(4000)=null output, @Parameter3 nvarchar(4000)=null output, @Parameter4 nvarchar(4000)=null output, @Parameter5 nvarchar(4000)=null output, @Parameter6 nvarchar(4000)=null output, @Parameter7 nvarchar(4000)=null output, @Parameter8 nvarchar(4000)=null output, @Parameter9 nvarchar(4000)=null output, @Parameter10 nvarchar(4000)=null output) as set nocount on; begin try begin tran declare @dec nvarchar(4000),@set nvarchar(4000),@update nvarchar(4000),@error nvarchar(200) select @input=replace(replace(@input,nchar(13)+nchar(10),nchar(32)),nchar(9),nchar(32)) while charindex(nchar(32)+nchar(32),@input)>0 select @input=replace(@input,nchar(32)+nchar(32),nchar(32)) while charindex(nchar(32)+'@',@input)>0 select @input=replace(@input,nchar(32)+'@','@') while charindex('@'+nchar(32),@input)>0 select @input=replace(@input,'@'+nchar(32),'@') if isnull(@s,'')!>'' return if object_id('tempdb..#Parameter') is not null drop table #Parameter select Row=row_number()over(order by t2.row), Parameter=left(ltrim(t2.Col),charindex(nchar(32),ltrim(t2.Col)+',@')-1), ParaType=rtrim(stuff(ltrim(t2.Col),1,charindex(nchar(32),ltrim(t2.Col)),'')), [Value]=cast(null as nvarchar(4000)), [Flag]=cast(null as bit), [IsChar]=cast(null as bit) into #Parameter from (select convert(xml,'<root><v>'+replace(@input,',@','</v><v>@')+'</v></root>'))T(c) outer apply (select row=1,Col=N.v.value('.','nvarchar(100)') from T.c.nodes('/root/v')N(v))T2 update #Parameter set ParaType=case when charindex(reverse(N'output'),reverse(ParaType))=1 then rtrim(left(ParaType,len(ParaType)-6)) when charindex(reverse(N'out'),reverse(ParaType))=1 then rtrim(left(ParaType,len(ParaType)-3)) else ParaType end update a 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,''))) then 1 when b.[IsChar]=0 and b.ID not in(106,108) and ParaType<>b.Name then 1 else 0 end from #Parameter a outer apply (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 if exists(select 1 from #Parameter where [Flag]=1) begin set @error='檢查格式是否正確' raiserror 50001 N'#' end update #Parameter set [Value]=case row when 1 then @Parameter1 when 2 then @Parameter2 when 3 then @Parameter3 when 4 then @Parameter4 when 5 then @Parameter5 when 6 then @Parameter6 when 7 then @Parameter7 when 8 then @Parameter8 when 9 then @Parameter9 when 10 then @Parameter10 end select @dec='',@set='',@update='' select @dec=@dec+case when row=1 then 'Declare ' else ' ,' end +Parameter+' '+ParaType, @set=@set+case when row=1 then 'select ' else ' ,' end +Parameter+'=' +case when [IsChar]=1 then isnull(quotename([Value],''''),'Null') else isnull(rtrim([Value]),'Null') end, @update=@update+case when row=1 then 'Update #Parameter set [Value]= case Row when ' else ' when ' end+rtrim(Row)+' then rtrim('+Parameter+')' from #Parameter order by row asc exec(@dec+' '+@set+' '+@s+' '+@update+' end') 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 exec sp_ms_marksystemobject 'Sp_ExecSQL' 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