OUTPUTパラメータの使用

用意するストアド

CREATE PROCEDURE PROC_TEST
  @i int,
  @o1 int OUTPUT,
  @o2 int OUTPUT
AS
BEGIN
  SELECT @i AS I, @o1 AS O1, @o2 AS O2
  SET @o1 = @i + 1
  SET @o2 = @i + @o2
END

DECLARE @o1 int
DECLARE @o2 int
SET @o2 = 2
EXEC PROC_TEST 1, @o1 OUTPUT, @o2 OUTPUT
SELECT @o1 as O1, @o2 as O2

実行結果

I    O1   O2
---------------
1    NULL 2

O1   O2
----------
2    3

ADO.NETからの実行

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Using con As New SqlConnection("Data Source=localhost;initial Catalog=TESTDB;User ID=sa;Password=****")
            Using cmd As New SqlCommand("PROC_TEST", con)
                cmd.CommandType = CommandType.StoredProcedure
                Dim i As New SqlParameter("@i", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.Input, .Value = 1}
                Dim o1 As New SqlParameter("@o1", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.Output}
                Dim o2 As New SqlParameter("@o2", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.InputOutput, .Value = 2}
                cmd.Parameters.Add(i)
                cmd.Parameters.Add(o1)
                cmd.Parameters.Add(o2)

                Console.WriteLine("i=" & i.Value & vbTab & "o1=" & o1.Value & vbTab & "o2=" & o2.Value)

                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    While rdr.Read
                        Console.WriteLine("i=" & rdr("i") & vbTab & "o1=" & rdr("o1") & vbTab & "o2=" & rdr("o2"))
                    End While
                End Using
                con.Close()

                Console.WriteLine("i=" & i.Value & vbTab & "o1=" & o1.Value & vbTab & "o2=" & o2.Value)
            End Using
        End Using
        Console.ReadLine()
    End Sub

End Module

実行結果

i=1     o1=     o2=2
i=1     o1=     o2=2
i=1     o1=2    o2=3

ADO.NETからの実行その2

Valueを与えればOutputはInputOutputでも通る。SqlParameterはnull(Nothing)が許されないのでDBNull.Valueを与える必要がある。

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Using con As New SqlConnection("Data Source=localhost;initial Catalog=TESTDB;User ID=sa;Password=****")
            Using cmd As New SqlCommand("PROC_TEST", con)
                cmd.CommandType = CommandType.StoredProcedure
                Dim i As New SqlParameter("@i", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.Input, .Value = 1}
                Dim o1 As New SqlParameter("@o1", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.InputOutput, .Value = DBNull.Value}
                Dim o2 As New SqlParameter("@o2", SqlDbType.Int) _
                    With {.Direction = ParameterDirection.InputOutput, .Value = 2}
                cmd.Parameters.Add(i)
                cmd.Parameters.Add(o1)
                cmd.Parameters.Add(o2)

                Console.WriteLine("i=" & i.Value & vbTab & "o1=" & o1.Value & vbTab & "o2=" & o2.Value)

                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    While rdr.Read
                        Console.WriteLine("i=" & rdr("i") & vbTab & "o1=" & rdr("o1") & vbTab & "o2=" & rdr("o2"))
                    End While
                End Using
                con.Close()

                Console.WriteLine("i=" & i.Value & vbTab & "o1=" & o1.Value & vbTab & "o2=" & o2.Value)
            End Using
        End Using
        Console.ReadLine()
    End Sub

End Module

実行結果

i=1     o1=     o2=2
i=1     o1=     o2=2
i=1     o1=2    o2=3

テーブル型のパラメータ

用意するストアド

CREATE TYPE TYPE_TEST AS TABLE 
( x int,
  y varchar(8));
GO

CREATE PROCEDURE PROC_TEST
  @tbl TYPE_TEST READONLY
AS
BEGIN
  SELECT y, COUNT(*) as cnt
  FROM @tbl
  GROUP BY y
  ORDER BY COUNT(*) DESC
END

DECLARE @tbl TYPE_TEST

INSERT INTO @tbl SELECT 1, 'A'
INSERT INTO @tbl SELECT 2, 'B'
INSERT INTO @tbl SELECT 3, 'C'
INSERT INTO @tbl SELECT 4, 'A'
INSERT INTO @tbl SELECT 5, 'C'
INSERT INTO @tbl SELECT 6, 'C'
INSERT INTO @tbl SELECT 7, 'A'
INSERT INTO @tbl SELECT 8, 'C'

EXEC PROC_TEST @tbl

実行結果

y    cnt
----------
C    4
A    3
B    1

ADO.NETからの実行

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Using con As New SqlConnection("Data Source=localhost;initial Catalog=TESTDB;User ID=sa;Password=****")
            Using cmd As New SqlCommand("PROC_TEST", con)
                cmd.CommandType = CommandType.StoredProcedure

                Dim dt As New DataTable
                Dim dr As DataRow
                dt.Columns.Add() : dt.Columns.Add()
                dr = dt.NewRow() : dr(0) = 1 : dr(1) = "A" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 2 : dr(1) = "B" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 3 : dr(1) = "C" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 4 : dr(1) = "A" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 5 : dr(1) = "C" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 6 : dr(1) = "C" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 7 : dr(1) = "A" : dt.Rows.Add(dr)
                dr = dt.NewRow() : dr(0) = 8 : dr(1) = "C" : dt.Rows.Add(dr)

                Dim tbl As New SqlParameter("@tbl", SqlDbType.Structured) _
                With {.Direction = ParameterDirection.Input,
                      .TypeName = "TYPE_TEST", .Value = dt}
                cmd.Parameters.Add(tbl)

                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    While rdr.Read
                        Console.WriteLine("y=" & rdr("y") & vbTab & "cnt=" & rdr("cnt"))
                    End While
                End Using
                con.Close()
            End Using
        End Using
        Console.ReadLine()
    End Sub

End Module

実行結果

y=C     cnt=4
y=A     cnt=3
y=B     cnt=1

カーソル

ストアド

CREATE TYPE TYPE_TEST AS TABLE 
( x int,
  y varchar(8));
GO

CREATE PROCEDURE PROC_TEST
  @tbl TYPE_TEST READONLY
AS
BEGIN
  DECLARE @x int
  DECLARE @y varchar(8)
  DECLARE @z varchar(8)

  DECLARE CSR CURSOR FOR SELECT * FROM @tbl
  OPEN CSR

  SET @z = ''
  FETCH NEXT FROM CSR INTO @x,@y
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF CHARINDEX(@y, @z) = 0
      SET @z = @z + @y

    FETCH NEXT FROM CSR INTO @x,@y
  END

  CLOSE CSR
  DEALLOCATE CSR

  SELECT @z
END

DECLARE @tbl TYPE_TEST

INSERT INTO @tbl SELECT 1, 'A'
INSERT INTO @tbl SELECT 2, 'C'
INSERT INTO @tbl SELECT 3, 'C'
INSERT INTO @tbl SELECT 4, 'A'
INSERT INTO @tbl SELECT 5, 'C'
INSERT INTO @tbl SELECT 6, 'B'
INSERT INTO @tbl SELECT 7, 'A'
INSERT INTO @tbl SELECT 8, 'C'

EXEC PROC_TEST @tbl

実行結果

(列名なし)
-----
ACB