Geekly Articles each Day

MS SQL 2011-返されたデータセットの変更

元のサウンドと生活の中で、この機能はWith Result Setのように聞こえます。 このことにより、ストアドプロシージャによって返されるデータセットの名前とデータ型を変更できます。

この機能の使用の詳細を掘り下げる前に、SQL Serverの以前のバージョンがストアドプロシージャによって返されたデータをどのように処理したかを調べます。 結果を処理するためにどのような機会を提供しましたか？

作業を示すために、例として3つの列で構成されるtbl_Testテーブルを使用します。



--  ,    IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Test' AND type = 'U') DROP TABLE tbl_Test GO SET ANSI_NULLS ON GO --   CREATE TABLE [dbo].[tbl_Test]( [Id] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [PhoneNumber] [int] NOT NULL ) ON [PRIMARY] GO

ここにいくつかの情報を書きます。 1000エントリにします。
 --     CTE ;With Cte(Id,Name,PhoneNo) As ( Select Id = 1 ,Name='Name' + CAST( 1 As Varchar(50)) ,PhoneNo=12345678 Union All Select Id+1 ,Name= 'Name' + CAST( Id+1 As Varchar(50)) ,PhoneNo+1 From Cte Where Id <1000 ) --      Insert Into dbo.tbl_test Select * From Cte Option( Maxrecursion 0) --   Select * From tbl_Test


上記のスクリプトを実行すると、およそ次のデータセット（一部）が出力されます
  ID名PhoneNumber
 1名前1 12345678
 2名前2 12345679
 3名前3 12345680
 4名前4 12345681
 5名前5 12345682
 ...


次に、クエリデータをtbl_Testテーブルに出力するプロシージャを作成します。
 CREATE PROCEDURE dbo.Usp_FetchRecords AS BEGIN Select Id ,Name ,PhoneNumber From dbo.tbl_Test END


ストアドプロシージャの結果からデータの最終セットを取得するには、いくつかの方法があります。 それらのいくつかはErland Sommarskogの彼の記事の記事で説明され議論されています 。 一時テーブルに基づくアプローチの1つを使用します。

一時テーブルを使用する


 -- #Temp    tempdb,    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END --   CREATE TABLE #Temp ( Id int ,EmpName Varchar(50) ,PhoneNo int ) --   ,    INSERT INTO #Temp ( Id ,EmpName ,PhoneNo ) EXEC dbo.Usp_FetchRecords --      Select * from #Temp

出力で必要な列とタイプが事前にわかっている場合、上記のアプローチは完全に機能します。

このアプローチおよび類似のアプローチの欠点：


新しいアプローチMS SQL 2011


新しいバージョンでは、これらの欠点を取り除くことができ、どのように正確に表示されるのかがわかります。
 EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Emp Name] varchar(50) ,[Phone Number] varchar(50) ) )

出力は次のようになります。
  Emp Id Emp Name電話番号
 1名前1 12345678 <
 2名前2 12345679 <
 3名前3 12345680 <
 4名前4 12345681 <
 5名前5 12345682 <
 ...


With Result Setを使用するための一般的な構文：
 WITH RESULT SETS( ( Column Name1 DataType [Size] ,Column Name2 DataType [Size] , . . . . . . . . . . . . , . . . . . . . . . . . . . . ,Column Name-n DataType [Size] ) , ( Column Name1 DataType [Size] ,Column Name2 DataType [Size] , . . . . . . . . . . . . , . . . . . . . . . . . . . . ,Column Name-n DataType [Size] ) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . , ( Column Name1 DataType [Size] ,Column Name2 DataType [Size] , . . . . . . . . . . . . , . . . . . . . . . . . . . . ,Column Name-n DataType [Size] ) )


したがって、結果のデータセットの列の名前を任意に変更できます。 暗黙的な型キャストが許可されているフレームワーク内のデータ型を変更できます。 そうでない場合、データベースはエラーを生成します。

したがって、次の例では、データベースは不正な型変換に関するエラーをスローします。 フィールドがvarchar（50）として宣言されている間にint型を返そうとしています。
 EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Emp Name] int --    int ,[Phone Number] varchar(50) ) )

スクリプトの実行中に、次のエラーが表示されます。

メッセージ8114、レベル16、状態2、プロシージャUsp_FetchRecords、行5データ型varcharからintへの変換エラー。

上記のデモは、結果セットを使用して単一の結果データセットを変換するように設計されています。 ただし、一般的な構文からわかるように、この手法は複数の結果セットに拡張できます。 これを行う方法の例があります。

2つのデータセットを返すストアドプロシージャがあるとします。
 CREATE PROCEDURE [dbo].[Usp_ModifiedFetchRecords] AS BEGIN Select Id ,Name ,PhoneNumber From dbo.tbl_Test; Select Id ,Name From dbo.tbl_Test Where PhoneNumber % 2 = 0 END


2番目の選択は、偶数の電話番号を持つサブスクライバーを返します。 実行例は次のとおりです（結果の一部）。



次に、With Result Setを使用して、ストアドプロシージャ自体を変更せずに、より消化しやすい結果を取得してみましょう。
 EXEC Usp_ModifiedFetchRecords WITH RESULT SETS( ( [Emp Id From First Result Set] int ,[Emp Name From First Result Set] varchar(50) ,[Phone Number From First Result Set] varchar(50) ), ( [Emp Id From Second Result Set] int ,[Emp Name From Second Result Set] varchar(50) ) )

実行の結果は次のようになります。


この場合、ストアドプロシージャはデータの2つの結果セットを返しますが、With Result Setでそのうちの1つだけを処理しようとすると、SQLエンジンからエラーが発生します。

メッセージ11535、レベル16、状態1、プロシージャUsp_ModifiedFetchRecords、行11のEXECUTEステートメントは、WITH RESULT SETS句が1つの結果セットを指定し、これより多くの結果セットを送信しようとしたため失敗しました。

結果セットを使用してデータを取得する方法


結果セットを使用して取得した値をさらに処理する必要がある場合があります。 この場合、一時テーブルまたはテーブルタイプ変数を使用できます。

テーブル型変数を使用するアプローチを検討してください。
 Declare @tblStoreWithResultSetsData Table ( [Employee Id] int ,[Employee Name] varchar(50) ,[Emp Phone No] int ) insert into @tblStoreWithResultSetsData EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Emp Name] varchar(6) --  , --     6. --    ,[Phone Number] varchar(50) ) ) Select * From @tblStoreWithResultSetsData

結果が期待され、従業員の名前は6文字に削減されます。 これは、次のスクリーンショットで確認できます（最後の10エントリ）



可能な応用


  1. SSISパッケージの データの変換 が簡単になります 。詳細については、こちらの記事を参照してください。
  2. スキーマを変更せずにデータ型を変更します 。 .Netアプリケーションがブール型の値を予期し、テーブル内の値がintまたはchar（1）としてエンコードされていると想像してください。 一般に、Case When Then Else構造を使用して値変換を適用できます。 ただし、データ型をすぐにビットに変更する方が簡単で快適です（intの場合）。
  3. With Result Setアプリケーションの別の例は、.Netプログラムがintを待機し、データベース内の列がfloat型である場合です。
  4. 回路変更 に対する DAL 耐性の可能性 。 これは、結果セットを使用する場合、結果のデータセットの列名を設定する場合、正の免疫を指します。 その場合、データベース自体で名前がどのように変化するかは関係ありません。 ストアドプロシージャの一種のVIEWアナログ。


制限事項


最終データセットで選択的な列の変更を行うことはできません。 たとえば、次のスクリプトは実行中にエラーを引き起こします。
 EXEC Usp_FetchRecords WITH RESULT SETS( ( [Emp Id] int ,[Phone Number] varchar(50) ) )

プロシージャは3列のセットを返すため。 エラーは次のようになります。

メッセージ11537、レベル16、状態1、プロシージャUsp_FetchRecords、行5 EXECUTEステートメントは、WITH RESULT SETS句が結果セット番号1に2列を指定したが、実行時に3列を送信したために失敗しました。

サイクルからの転送：
MS SQL Server 2011： スタンドアロンデータベース 、 新しいシーケンスオブジェクト 、 オフセットステートメント 、 エラー処理 、 結果セット構成 、 SSMSの新機能 。

Source: https://habr.com/ru/post/J123573/

More articles:


All Articles