トピックに精通している人のために、5番目の方法まで投稿をスクロールすることをお勧めします。
データを取得する必要がある手順を次のようにします。
create procedure Proc1 as begin select 1 p1, 'b' p2 end
1方法
最も簡単な方法の1つ。
insert ... exec ...
コンストラクトの使用
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(p1 int, p2 varchar(max)) insert #t1 exec Proc1 select * from #t1
長所と短所：
- 送信されたフィールドは2回リストされ
select
（これらは内部select
、外部テーブル作成、およびinsert
）。 さらに、新しい同様の呼び出しごとにフィールドの列挙が発生します。 （この基準を追加します。多数の編集とプロシージャを呼び出す場所が多いため、出力データを変更するプロセスは非常に時間がかかるためです） - 重大な制限があります-テーブルは1つしか取得できません
- 手順が単純な出力モードで機能するためには、追加の手順は必要ありません。
insert
せずにexec Proc1
を実行するだけです
2方法
以前に作成したテーブルに書き込む。 ここで、プロシージャに挿入を追加する必要があります。
create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end
基本的に、プロシージャ内に挿入行をラップします。
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(p1 int, p2 varchar(max)) exec Proc1 select * from #t1
長所と短所：
- 転送されたフィールドは2回リストされ、新しい使用ごとにもう1回転送されます
- プロシージャを単純な出力モードで動作
Proc1
は、Proc1
から受け取っProc1
テーブルをProc1
する別のプロシージャを記述するか、Proc1
内でそれらをProc1
出力するかを決定する必要があります。 たとえば、挿入用のテーブルが存在しないという事実に基づいて：
alter procedure Proc1 as begin declare @show bit if object_id(N'tempdb..#t1',N'U') is null begin set @show = 1 create table #t1(p1 int, p2 varchar(max)) end insert #t1(p1, p2) select 1 p1, 'b' p2 if (@show = 1) begin select * from #t1 end end
私は、永続的なテーブルを介して転送する可能性を考慮していません。 必要に応じて、タスクはこのトピックに関係しません。 そうでない場合は、セッション間のロックと認証に関する不要な問題が発生します。
3方法
実際、これは2番目の方法の改良版です。 サポートを簡素化するには、カスタムテーブルタイプを作成します。 次のようになります。
create type tt1 as table(p1 int, p2 varchar(max)) go create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end go -- : declare @t1 tt1 if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 select * into #t1 from @t1 exec Proc1 select * from #t1
長所と短所：
- 送信されるフィールドは2回リストされますが、新しい使用ごとに複雑さは増しません
- 結果の直接出力を整理するには、追加のアクションも必要です。
- インデックスと制限の作成には、次のような小さな困難があります。
select ... into
コンストラクトを使用して作成することはできません
4方法
3番目の方法の複雑さ。制限とインデックスを持つテーブルを作成できます。 前のものとは異なり、Microsoft SQL Server 2005で動作します。
create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end go create procedure Proc1_AlterTable as begin alter table #t1 add p1 int, p2 varchar(max) alter table #t1 drop column delmy end go -- : if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(delmy int) exec Proc1_AlterTable exec Proc1 select * from #t1
ただし、通常、delmy一時列は使用されず、代わりに1つの最初の列（ここではp1）でテーブルが作成されます。
長所と短所：
- 送信されるフィールドは2回リストされますが、新しい使用ごとに複雑さは増しません
- 結果を直接出力するには、追加の結果も必要です。
- 未知の理由により、
alter table #t1
コンストラクトでロックが発生することがあり、プロセスはProc1
（Proc1_AlterTable
はない！）の並列リクエストが完了することを予期することが予期せずに発見されました。 これが何に関連しているのか誰もが知っているなら、それを共有してください、私は聞いてうれしいです:)
5方法
このメソッドは、事前に作成されたプロシージャを使用します。 これは、実行中のプロシージャに動的SQLクエリを含めることに基づいています。 ただし、非常に使いやすいです。
それを使用するには、手順を次のように処理する必要があります。
1.手順の最初に、次の行を含めます。
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin
2.出力を選択するすべてのプロシージャを再実行して、
into #Output5
で始まる一時テーブルを作成します（たとえば、
into #Output5
、
into #Output5
、
into #Output5
に）。 プロシージャが結果セットを作成しない場合、アクションは不要です。
3.手順の最後に、次の行を含めます。
exec util.InclusionEnd -- , #Output, util.InclusionBegin
この例では、次を取得します。
create procedure Proc1 as begin if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin select 1 p1, 'b' p2 into #Output1 exec util.InclusionEnd -- , #Output, util.InclusionBegin end
起動は次のとおりです。
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max)) exec util.InclusionRun' select * from #InclusionOutput1 ', 1, '#InclusionOutput' exec Proc1
生成されたSQLは常に良いとは限らないため、上記の例は小さな命令に適しています。 大量のコードがある場合は、それを別のプロシージャに削除して動的部分からのみexec呼び出しを実行するか、データを新しい一時テーブルにリロードできます。 後者の場合、当然、別の「余分な」コピーが発生しますが、この段階で結果を事前にグループ化し、さらに処理するために必要なフィールドのみを選択できることがよくあります（たとえば、返されるデータがすべて不要な場合など） ）
util.InclusionRun
関数には3つのパラメーターが渡されます。
-
@sql
呼び出されたプロシージャ内で実行するSQLスクリプト -
@notShowOutput
-if = 1の場合、#Output
始まるテーブルの出力をブロックします -
@replaceableTableName
（default ='#Output'
）@sql
で使用されるテーブルの名前にプレフィックスを設定し、スクリプト内の対応する@sql
#Output*
テーブルに置き換えます。 たとえば、#Output55
を指定し、2つのテーブル#Output55
および#Output0A
プロシージャで作成されている場合、@sql
では、#Output0A
を#InclusionOutput2
として、#Output0A
を#InclusionOutput2
として#InclusionOutput2
作業は、最初に
Proc1
を実行せずに
Proc1
を実行すると、処理前に出力されるすべてのデータを出力するプロシージャの自然な操作につながるように構成されています。
使用のニュアンス：
- プロシージャでの
return
使用に制限を課します。 その前にutil.InclusionEnd
を実行する必要があります - 起動されたプロシージャから結果を出力するセレクターは、呼び出される前に作成された＃出力テーブルよりも早く結果を出力します（出力は
util.InclusionEnd
のみ発生するため、これは論理的util.InclusionEnd
）
長所と短所：
- 渡されたフィールドは一度リストされますが、新しく使用するたびに複雑さは増しません
- 結果を直接出力するためのアクションは不要です。
- 使用のニュアンスを覚えて考慮する必要があります
- 追加の手順により、より多くの命令が実行され、頻繁な呼び出しのパフォーマンスが低下する可能性があります（これは起動時に1秒に1回未満は無視できると思います）
- おそらく、このメソッドに慣れていない従業員のコードの理解が複雑になる可能性があります。プロシージャは2つのexec-callを取得し、すべての
#Output
テーブルが表示されるという非自明性を取得します。 - 外部ツールなしでユニットテストを簡単に整理できます
使用のデモンストレーション：
非表示のテキスト
コード：
結果：
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
その他の方法
（
OUTPUT
）関数からのパラメーター転送を使用して、その値に基づいてテーブルを復元できます。 たとえば、カーソルまたはXMLを渡すことができます。
このテーマに関する記事があります。
このタスクにカーソルを使用するには、最初にカーソルが必要な場合にのみ理由がわかります。 しかし、XMLは有望に見えます。 以下は、非常に興味深いパフォーマンステストの結果です。
このタスクを簡素化するために使用する方法を聞くのは興味深いです:)
UPD 03/31/2014：コメントからのアイデアに関する投稿を修正