ストアドプロシージャの結果を取得する。

スクリプトからストアドプロシージャの結果を取得する。
MSDEでは(SQL Server 7.0も）ストアドファンクション、いわゆるユーザ定義関数はない。
SQL Server 2000 ではサポートされたらしいけど。

ストアドプロシージャから値を取得する方法は３通りある。
１つ目はストアドプロシージャの返り値を取得する方法。
ストアドプロシージャは整数型限定だが値を返すことができるのでこれを利用する。
２つ目はストアドプロシージャの結果セットを使う方法。
ストアドプロシージャ内でSELECT文を実行するとその結果をストアドプロシージャの結果セットとして取得することができる。
３つ目はストアドプロシージャが実際のテーブルの値を変化させて、その結果をあとから取得する方法。
この３つめの方法は自明かつあたりまえなのでここでは試してみない。

●ストアドプロシージャの返り値を取得する

まず、ストアドプロシージャの返り値をスクリプトから取得しよう。
ストアドプロシージャのテストをしたときにGetAuthorAge というプロシージャを作ったのでこれを使ってみることにする。
一応再掲しておこう。

1> CREATE PROCEDURE GetAuthorAge (@BookID INTEGER ) AS
2>    DECLARE @AuthorAge INTEGER
3>    SET @AuthorAge = -1
4>    IF ( SELECT COUNT(*) FROM BookTable WHERE BookID=@BookID ) > 0
5>        SELECT @AuthorAge = AuthorTable.Age From AuthorTable, BookTable
6>               WHERE BookTable.BookID = @BookID
7>    RETURN @AuthorAge
8> GO

こんな感じだった。
実行するときはこんな感じ。

1> DECLARE @Result INTEGER
2> EXECUTE @Result = GetAuthorAge 3
3> PRINT @Result
4> GO
10
1>

さて、ここでは値をスクリプト内で取得しなければいけない。
PRINT文を飛ばしても意味がないので、SELECT文を使う。
T-SQLでは文区切りがないので、複数文をひとつのSQL文にして飛ばすときには、複数文を続けて書くという恐ろしいことをする。
実行するSQL文はこんな感じになる。
"DECLARE @Result INTEGER EXECUTE @Result = GetAuthorAge 3 SELECT @Result"

というわけでスクリプトはこんな感じ。
MSDE-S02.vbs

' ADODB を使ったサンプル
' Stdoutを使うので、CScriptで実行すること
' ストアドプロシージャの返り値を取得する。

Dim objConn, objRS
set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open ( "Provider=SQLOLEDB.1;Password=kurokuro;User ID=kuro;Initial Catalog=KuroDB" )

set objRS = objConn.Execute( _
   "DECLARE @Result INTEGER EXECUTE @Result = GetAuthorAge 3 SELECT @Result" )

If objRS.EOF Then
   Wsh.Stdout.Write "No Result" & vbCRLF
Else
   Wsh.Stdout.Write "Result = " & objRS(0) & vbCRLF
End If

実行結果は以下のようになる。結果の「10」が取得できている。

C:\Temp>cscript -nologo msde-s02.vbs
Result = 10

C:\Temp>

●結果セットを使う。

次に結果セットを使ってストアドプロシージャの結果を取得してみよう。
ストアドプロシージャの中でSELECT文を使うと、その結果を呼び出し側から取得できる。

とりあえず、AuthorTableの中身を全部取得するプロシージャを作ってみよう。

1> CREATE PROCEDURE GetAllAuthors AS
2>      SELECT * FROM AuthorTable
3> GO

これを使ったスクリプトは以下のようになる。
普通にSELECT文を実行したときと同じ扱いでいい。
スクリプトそのものは、MSDE-S01.vbsをちょっと改良しただけ。
MSDE-S03.vbs

' ADODB を使ったサンプル
' Stdoutを使うので、CScriptで実行すること
' ストアドプロシージャの結果セットを使う。

Dim objConn, objRS
set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open ( "Provider=SQLOLEDB.1;Password=kurokuro;User ID=kuro;Initial Catalog=KuroDB" )

set objRS = objConn.Execute( "EXEC GetAllAuthors" )

Wsh.Stdout.Write "* Fields of ResultSet * " & vbCRLF
For i=0 to objRS.Fields.Count -1
    Wsh.Stdout.Write "Field(" & i & "): "& objRS.Fields(i).name & vbCRLF
Next 
Wsh.Stdout.Write vbCRLF

Wsh.Stdout.Write "* Data in ResultSet * " & vbCRLF
Do While Not objRS.EOF
    For i=0 to objRS.Fields.Count -1
        Wsh.Stdout.Write objRS.Fields(i).name & ":"
        Wsh.Stdout.Write objRS(i) & "  "
    Next 
    Wsh.Stdout.Write vbCRLF

    objRS.MoveNext
Loop

実行してみる。

C:\Temp>cscript -nologo msde-s03.vbs
* Fields of ResultSet *
Field(0): AuthID
Field(1): Name
Field(2): Age

* Data in ResultSet *
AuthID:101  Name:太郎さん              Age:100
AuthID:102  Name:次郎さん              Age:50
AuthID:103  Name:三郎さん              Age:10

C:\Temp>

おお。動いた動いた。

●複数の結果セットを使う。

結果セットは１つではなく複数持つことができるらしい。
つまりストアドプロシージャの中で複数回SELECT文を使うと、その結果を全部呼び出し側から取得できる。

次のレコードセットを取得するには、レコードセットオブジェクトの、NextRecordsetメソッドを使う。
MoveNextは、「レコードをひとつ進める」のに対して、 NextRecordsetは「次のレコードセットを取得する」ということに注意。

こんどは、AuthorTableとBookTableの中身を全部取得するプロシージャを作ってみよう。

1> CREATE PROCEDURE GetAllAuthorsAndBooks AS
2>     SELECT * FROM AuthorTable
3>     SELECT * FROM BookTable
4> GO

これを使ったスクリプトは以下のようになる。
さっきの、MSDE-S03.vbsをちょっと改良してループを入れただけ。
MSDE-S04.vbs

' ADODB を使ったサンプル
' Stdoutを使うので、CScriptで実行すること
' ストアドプロシージャの結果セットを使う。

Dim objConn, objRS
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.Open ( "Provider=SQLOLEDB.1;Password=kurokuro;User ID=kuro;Initial Catalog=KuroDB" )

Set objRS = objConn.Execute( "EXEC GetAllAuthorsAndBooks" )

Do While Not objRS Is Nothing  

    Wsh.Stdout.Write "**** Record Set Found ****" & vbCRLF

    Wsh.Stdout.Write "* Fields of ResultSet * " & vbCRLF
    For i=0 to objRS.Fields.Count -1
        Wsh.Stdout.Write "Field(" & i & "): "& objRS.Fields(i).name & vbCRLF
    Next 
    Wsh.Stdout.Write vbCRLF

    Wsh.Stdout.Write "* Data in ResultSet * " & vbCRLF
    Do While Not objRS.EOF
        For i=0 to objRS.Fields.Count -1
            Wsh.Stdout.Write objRS.Fields(i).name & ":"
            Wsh.Stdout.Write objRS(i) & "  "
        Next 
        Wsh.Stdout.Write vbCRLF

        objRS.MoveNext
    Loop
    Wsh.Stdout.Write vbCRLF
    Set objRS = objRS.NextRecordSet()
Loop

実行してみる。

C:\Temp>cscript -nologo msde-s04.vbs
**** Record Set Found ****
* Fields of ResultSet *
Field(0): AuthID
Field(1): Name
Field(2): Age

* Data in ResultSet *
AuthID:101  Name:太郎さん              Age:100
AuthID:102  Name:次郎さん              Age:50
AuthID:103  Name:三郎さん              Age:10

**** Record Set Found ****
* Fields of ResultSet *
Field(0): BookID
Field(1): Title
Field(2): AuthID
Field(3): Price

* Data in ResultSet *
BookID:1  Title:本その１              AuthID:102  Price:340
BookID:2  Title:本その２              AuthID:101  Price:10
BookID:3  Title:本その３              AuthID:103  Price:840
BookID:4  Title:Book No.Four          AuthID:101  Price:9840

C:\Temp>

おお。動いた動いた。
このNextRecordsetメソッドでは、もとのSQL文が複合ステートメントだったときには、 NextRecordSet実行時点でステートメントが実行されるとか、
リモートサーバからマーシャリングがすんでいないときにはレコードセットが取得できないとか、いろいろ複雑なことがからんでいるようだが、ここではこれでよしとしよう。

●システムプロシージャの結果の取得

ここでやったやり方はそのままシステムプロシージャの結果取得に使えるはずだ。

さっきの、MSDE-S04.VBSのSQLを実行するところを、
Set objRS = objConn.Execute( "sp_helprole" )
に変えて実行してみよう。

C:\Temp>cscript -nologo msde-s04s.vbs
**** Record Set Found ****
* Fields of ResultSet *
Field(0): RoleName
Field(1): RoleId
Field(2): IsAppRole

* Data in ResultSet *
RoleName:public  RoleId:0  IsAppRole:0
RoleName:db_owner  RoleId:16384  IsAppRole:0
RoleName:db_accessadmin  RoleId:16385  IsAppRole:0
RoleName:db_securityadmin  RoleId:16386  IsAppRole:0
RoleName:db_ddladmin  RoleId:16387  IsAppRole:0
RoleName:db_backupoperator  RoleId:16389  IsAppRole:0
RoleName:db_datareader  RoleId:16390  IsAppRole:0
RoleName:db_datawriter  RoleId:16391  IsAppRole:0
RoleName:db_denydatareader  RoleId:16392  IsAppRole:0
RoleName:db_denydatawriter  RoleId:16393  IsAppRole:0

C:\Temp>

動いた、動いた。

実は、最初は、sp_helprolemember db_owner としてやったのだが、結果セットの中のSIDフィールドの型がVARBINARYになっていて、
Wsh.Stdout.Write objRS(i)
で失敗してしまった。

バイナリ型のデータは表示するときにちょっと細工をする必要があるみたい。

MSDE 実験ページへ戻る。