New T-SQL in Denali (後編)

1. 結果セットの型定義

　Denaliでは、EXECUTEステートメントの結果セットに対して、あらかじめ型を定義できるオプションWITH RESULT SETSがサポートされます。これまでは、EXECUTEステートメントで実行されるSQL文やストアドプロシージャの結果セットについては型指定ができず、実行されるまでどのような結果セットが戻されるかはわかりませんでした。Denaliでは、EXECUTEステートメントのWITH RESULT SETSオプションを使用することで、結果セットの型を定義することができます。結果セットの型を定義できるようになることで、定義とは異なる結果セットが戻された場合にエラーとすることが可能になります。

　では、WITH RESULT SETSオプションを紹介していきましょう。 以下はWITH RESULT SETSオプションのシンタックスです。(WITH RESULT SETSオプションの詳細についてはこちらを参照ください)

EXECUTE <proc|clr proc|remote proc|function> [WITH <execute_option>[,...n ]]
{ RESULT SETS {UNDEFINED|NONE|(<result_sets_definition>)} }
 <result_sets_definition> ::= 
{ <result_set_definition> |AS OBJECT [<object_location>.]{table_name| view_name | tvf}
|AS TYPE [schema_name.]table_type_name | AS FOR XML  | (…) [,...n ]}

　RESULT SETSオプションのパラメータには、NONEまたは、結果セットの型を指定します。NONEは、結果セットが1件も戻されないことを定義します。つまり、NONEを指定した場合、EXECUTEの結果に結果セットが含まれているとエラーになります。

　では、WITH RESULT SETSオプションで、結果セットの型を定義する例を見ていきます。以下の例は、EXECUTEステートメントの結果セットに、カラム名がC1のINT型で戻されることを定義しています。実行するとわかりますが、この例では、期待通りの結果セットが戻されるのでエラーとなりません。

EXECUTE('SELECT 100 AS C1')
WITH RESULT SETS 
( 
   (C1 INT)
);

　これを、以下のようにdatetime型の結果セットを返すように変更してみます。

EXECUTE('SELECT GETDATE() AS C1')
WITH RESULT SETS 
( 
   (C1 INT)
);

　すると、今度は定義している型と異なる結果セットが戻されるため、エラーとなります。

Msg 11538, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified type 'int' for 
column #1 in result set #1, and the corresponding type sent at run time was 'datetime'
; there is no conversion between the two types.

　また、以下のように2つのカラムを返えすよう変更してみます。

EXECUTE('SELECT 100 AS C1, 200 AS C2')
WITH RESULT SETS 
( 
   (C1 INT)
);

　これもまたエラーとなります。結果セットのカラム数が異なるからです。

Msg 11537, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for
result set number 1, but the statement sent 2 column(s) at run time.

　さらに、結果セットを2つ返すように変更してみます。

EXECUTE('SELECT 100 AS C1; SELECT 200 AS C1')
WITH RESULT SETS 
( 
   (C1 INT)
);

　1つめのステートメントは成功しますが、型定義で指定している結果セットより多い結果セットの数が多いので、2つ目の結果セットを返すステートメントの実行時にエラーとなっています。

C1
-----------
100

(1 row(s) affected)

Msg 11535, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s),
and the statement tried to send more result sets than this.

　では、結果セットのカラム名と型定義で指定したカラム名が異なるとどうなるのでしょうか。

EXECUTE('SELECT 100 AS TEST')
WITH RESULT SETS 
( 
   (C1 INT)
);

　この場合、エラーにはならず最終的に結果セットは、型指定したカラム名が採用されます。

C1
-----------
100

(1 row(s) affected)

　また、型定義を結果セットの型と暗黙変換可能な型に変更してみます。

EXECUTE('SELECT 100 AS C1')
WITH RESULT SETS 
( 
   (C1 VARCHAR(5))
);

　この場合も、エラーとはならず、暗黙的に型変換が実行され結果セットは定義した型のデータが返されます。

EXECUTE('SELECT 100 AS C1')
WITH RESULT SETS 
( 
   (C1 VARCHAR(5))
);

　まとめると、定義した型が結果セットと同じではない、または、暗黙変換できない型だとエラーになる。また、カラム数、結果セット数が異なるとエラーになる。しかし、定義したカラム名は結果セットのカラム名と同じである必要はない、ということになります。

　このようにWITH RESULT SETSオプションを使用することで、結果セットの型を定義できるようになり、より安全なT-SQLプログラミングが可能となります。それだけではなく、場合によっては、結果セットのカラム名を任意のカラム名に変更することも可能です。WITH RESULT SETSには、NULLを許容するかどうかといったことも指定できるので、例えば、あるストアドプロシージャを実行するときに、通常はNULLを含む結果セットが戻されても問題ないが、ある特定のパラメータを指定した場合はNULLが戻されては困る、といったときに、呼び出し側であらかじめ定義しておくことも可能です。その他にも、AS OBJECT句を使用して特定のテーブルを指定することや、XMLの結果セット指定することもできます。詳細についてはこちらを参照ください。