संग्रहीत कार्यविधि से सारणीबद्ध डेटा पास करना

 if object_id('dbo.TestInclusion') is not null drop procedure dbo.TestInclusion go create procedure dbo.TestInclusion @i int as begin if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin if object_id('tempdb..#tmp2', 'U') is not null drop table #tmp2 select @i myI into #tmp2 if object_id('tempdb..#tmp3', 'U') is not null drop table #tmp3 select @i + 1 myI into #tmp3 select * into #Output0 --  (  util.InclusionEnd) from #tmp2 union all select * from #tmp3 select ' TestInclusion' alt into #OutputQwerty --  (  util.InclusionEnd) exec util.InclusionEnd --     #Output       util.InclusionBegin end go set nocount on set ansi_warnings off if object_id('tempdb..#ttInclusionParameters', 'U') is not null drop table #ttInclusionParameters go select ' 1:  TestInclusion.         myI   : 2  3.    1 : " TestInclusion"' exec dbo.TestInclusion 2 go select ' 2:  TestInclusion.         testSum   : 5' if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max)) exec util.InclusionRun ' select sum(myI) testSum from #InclusionOutput1 ', 1, '#InclusionOutput' exec dbo.TestInclusion 2 

 -----------------------------------------------------------------------------------------------------------------------------------------------------------  1:  TestInclusion.         myI   : 2  3.    1 : " TestInclusion" myI ----------- 2 3 alt -----------------------  TestInclusion ------------------------------------------------------------------------------------------------------  2:  TestInclusion.         testSum   : 5 testSum ----------- 5 

 if not exists(select top 1 null from sys.schemas where name = 'util') begin exec ('create schema util') end go alter procedure util.InclusionBegin as begin /*   : 1.       1.1.     : if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin 1.1.   select'         #Output ( into #Output, into #Output5, into #OutputMySelect) 1.2.     : exec util.InclusionEnd --  ,   #Output,       util.InclusionBegin 2.  ,    ,       (      #Output* ): if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int) exec util.InclusionRun('<sql         >')  .   util.InclusionRun */ set nocount on set ansi_warnings off declare @lvl int if object_id('tempdb..#ttInclusionParameters', 'U') is not null begin select @lvl = max(lvl) from #ttInclusionParameters -- null ,           if (@lvl is not null) begin insert #ttInclusionParameters(lvl, pr) select @lvl+1 lvl, null pr end end if object_id('tempdb..#ttInclusion', 'U') is not null begin --     #Output,   util.InclusionEnd    insert #ttInclusion(lvl, i) select isnull(@lvl, 0), so.object_id i from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and not exists (select top 1 null from #ttInclusion where i = so.object_id) end end GO go alter procedure util.InclusionEnd as begin /*   : 1.       1.1.     : if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin 1.1.   select'         #Output ( into #Output, into #Output5, into #OutputMySelect) 1.2.     : exec util.InclusionEnd --  ,   #Output,       util.InclusionBegin 2.  ,    ,       (      #Output* ): if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int) exec util.InclusionRun('<sql         >')  .   util.InclusionRun */ set nocount on set ansi_warnings off ---------------------------------------------------------------------------------------------------- --  declare @lvl int , @p0 varchar(max) --(@sql) sql     , @p1 varchar(max) --(@notShowOutput)   '1'        ,    #Output,    , @p2 varchar(max) --(@replaceableTableName)    if object_id('tempdb..#ttInclusionParameters', 'U') is not null begin --   select @p1 = max(val) from #ttInclusionParameters where pr = 1 --      (max(lvl) -    null     util.InclusionBegin) select @lvl = max(lvl) - 1 from #ttInclusionParameters if @lvl is not null begin -- select @p0 = max(case when pr = 0 then val end) , @p2 = max(case when pr = 2 then val end) from #ttInclusionParameters where lvl = @lvl having max(pr) is not null --   ,    ,   null- delete #ttInclusionParameters where lvl >= @lvl and (lvl > @lvl or @p0 is not null) end end ---------------------------------------------------------------------------------------------------- --    #Output if object_id('tempdb..#InclusionOutputs', 'U') is not null drop table #InclusionOutputs create table #InclusionOutputs(i int, tableName varchar(max), num int) if object_id('tempdb..#ttInclusion', 'U') is not null begin insert #InclusionOutputs(i, tableName, num) select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and so.name like '#Output%' and not exists (select top 1 null from #ttInclusion where i = so.object_id and lvl <= isnull(@lvl, lvl)) --   ,     delete #ttInclusion where lvl <= @lvl end else begin insert #InclusionOutputs(i, tableName, num) select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and so.name like '#Output%' end ---------------------------------------------------------------------------------------------------- --  (    -   #Output) declare @srcsql varchar(max) --    util.InclusionRun if (@p0 is not null and @p0 <> '') begin --  @replaceableTableName if (@p2 is not null and @p2 <> '') begin select @p0 = replace(@p0, @p2 + cast(num as varchar(10)), replace(tableName, '#', '#<tokenAfterReplace>')) from #InclusionOutputs order by num desc select @p0 = replace(@p0, '<tokenAfterReplace>', '') end --   select @srcsql = isnull(@srcsql + ' ' + char(13), '') + @p0 + ' ' + char(13) end --  #Output  if (@p1 is null or @p1 <> '1') --  1,   ! begin --    select @srcsql = isnull(@srcsql + ' ' + char(13), '') --   select @srcsql = isnull(@srcsql + ' ', '') + 'select * from ' + tableName from #InclusionOutputs order by num asc end if (@srcsql is not null) begin exec (@srcsql) end end go alter procedure util.InclusionRun @sql varchar(max), --sql       ( util.InclusionEnd) @notShowOutput bit, -- = 1,       #Output @replaceableTableName varchar(100) = '#Output' --        @sql,      #Output*   . -- ,   #InclusionOutput,       #Output55  #Output0A, --   @sql    #Output55   #InclusionOutput1,   #Output0A   #InclusionOutput2 as begin set nocount on set ansi_warnings off if object_id('tempdb..#ttInclusionParameters', 'U') is null begin print ' util.InclusionRun  , ..      #ttInclusionParameters! ' return end declare @lvl int select @lvl = isnull(max(lvl), 0) + 1 from #ttInclusionParameters insert #ttInclusionParameters(lvl, pr, val) select @lvl, 0, @sql union all select @lvl, 1, '1' where @notShowOutput = 1 union all select @lvl, 2, @replaceableTableName end