おやじプログラマー日記

WITH句 の中で自分自身を読んでやれば再帰的に処理が行われる様です。 以下のSQLでは連番を生成する処理を再帰処理で行っています。
WITH SEQLIST(SEQNO) as( SELECT 1 UNION ALL SELECT SEQNO + 1 FROM SEQLIST WHERE SEQNO < 10 ) SELECT * FROM SEQLIST;
このSQLを実行すると以下の様になります。

但し、このままでは100件までの再帰処理しかできない様で、「SEQNO < 102」と指定すると以下の様にエラーが発生します。
メッセージ 530、レベル 16、状態 1、行 1
ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。

このエラーも OPTION (MAXRECURSION nnn) として指定し「nnn」に101以上を指定すればできることになります。 WITH SEQLIST(SEQNO) as( SELECT 1 UNION ALL SELECT SEQNO + 1 FROM SEQLIST WHERE SEQNO < 102 ) SELECT * FROM SEQLIST OPTION (MAXRECURSION 1000);
尚、 MAXRECURSION の範囲は「0」～「32767」で「0」指定は制限が無い様です。 制限無しでもあまりに大きい値は問題有りだとは思います。

SELECT文において副問合せを使うことはよくありますが、 全く同じ副問合せを何度も記述することはSQL的にも非効率です。
そこで WITH句 の登場なのですが、 この句を用いて何度も利用するSELECT文を宣言し、 それをその後に続くSQLの中の FROM句 の中で利用できます。
以下の様なテストテーブル「TEST1」があるとします。 このテーブルに対して WITH句 を使ってみます。

TEST1のCODE2が「3」のデータを全て抽出するSELECTを WITH句 として宣言し、 さらにその WITH句 を2回使用するSQLが以下の様になります。
WITH TEST_WITH AS ( SELECT CODE1, CODE2, DATA1 FROM TEST1 WHERE CODE1 IN (1, 4) ) SELECT * FROM TEST_WITH WHERE CODE2 = 1 UNION SELECT * FROM TEST_WITH WHERE CODE2 = 3;
WITH句 では「CODE1」が「1」と「4」のデータを抽出します。 それ以降の SELECT文 では「CODE2」が「1」と「3」のものを UNION しています。 このSQLを実行すると以下の様になります。

SQL-Server には Oracle の様に CREATE OR REPLACE TABLE がありません。
そこで以下の様に OBJECT_ID 関数でテーブルの存在確認をし、存在すればテーブルを削除（DROP TABLE）します。
その後で、テーブルの生成（CREATE TABLE）を行います。
このテーブルでは CODE1 、 CODE2 を主キーとして設定する例を示しています。
IF OBJECT_ID('dbo.TEST1') IS NOT NULL DROP TABLE [TEST1]; CREATE TABLE [TEST1]( [CODE1] [NUMERIC]( 8) NOT NULL ,[CODE2] [NUMERIC]( 6) NOT NULL ,[DATA1] [VARCHAR](10) NULL ,[DATA2] [VARCHAR](20) NULL ,[DATA3] [NUMERIC]( 8) NULL ,PRIMARY KEY(CODE1, CODE2) );

ADO.NET を使用した SQL-Server のテーブルをCSVファイルに出力する簡単な関数を作成してみました。

CSVファイルを作成する関数への引数は、データベース名とデータソース名と、SQL文、それとCSVファイル名とCSV内のカラムヘッダを 出力するかのフラグになります。

CSV出力処理関数内では SQL-Server への接続をオープンし、指定されたSELECT文を実行します。 SELECT文の結果の行が在れば、1行づつデータを取得します。 初回の行の処理の場合には、リーダーオブジェクトからカラム名を取得しヘッダ行を書き込みます。 その後、各データ行をCSVファイルに書き込みます。 CSVの各データは強制的にダブルクォートで囲む様にしていますので、 データ内のダブルクォートはエスケープ処理しています。

Public Class Form1 'フォーム上に張り付けたボタンでの処理 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlToCsv("TEST", ".\SQLEXPRESS12", "SELECT * FROM TEST1 ORDER BY CODE", "TEST.CSV", True) End Sub ''' <summary>指定SQL文のCSVファイル出力</summary> ''' <param name="Database">データベース名</param> ''' <param name="DataSource">データソース名</param> ''' <param name="SQL">SELECT文</param> ''' <param name="CsvFilePath">CSVファイルパス</param> ''' <param name="fHeader">カラムヘッダ出力指示</param> Private Sub SqlToCsv(ByVal Database As String, ByVal DataSource As String, ByVal SQL As String, _ ByVal CsvFilePath As String, ByVal fHeader As Boolean) 'CSVファイル書込Encoding(Shift-JIS) Dim Enc As System.Text.Encoding = System.Text.Encoding.GetEncoding("Shift_JIS") 'CSVファイルオープン Dim SWrt As New System.IO.StreamWriter(CsvFilePath, False, Enc) 'SQL-Server接続文字列 Dim DBConnection As String = "Persist Security Info=false;Integrated Security=SSPI;" & _ "Database=" & Database & ";Data Source=" & DataSource & ";" 'SQLコネクションクラス Dim Conn As New System.Data.SqlClient.SqlConnection(DBConnection) Conn.Open() 'コマンドオブジェクト Dim Cmd As New System.Data.SqlClient.SqlCommand(SQL, Conn) 'TESTデータ取得(リーダーオブジェクト) Dim Reader As System.Data.SqlClient.SqlDataReader = Nothing Reader = Cmd.ExecuteReader() '読込行数が存在?? If Reader.HasRows Then Dim fFirst As Boolean = True '読込 While Reader.Read() = True If fHeader = True And fFirst = True Then fFirst = False Dim ColName As String = "" 'カラム数分の処理 For i As Integer = 0 To Reader.FieldCount - 1 'カラム名取得し、ダブルクォートで囲む ColName = SuroundDblQuotes(Reader.GetName(i)) 'カラム名書込 SWrt.Write(ColName) 'カンマ書込 If i < Reader.FieldCount - 1 Then SWrt.Write(","c) End If Next '改行書込 SWrt.Write(vbCrLf) End If Dim ColData As String = "" For i As Integer = 0 To Reader.FieldCount - 1 'データ取得し、ダブルクォートで囲む ColData = SuroundDblQuotes(Reader.GetValue(i).ToString()) 'データ書込 SWrt.Write(ColData) 'カンマ書込 If i < Reader.FieldCount - 1 Then SWrt.Write(","c) End If Next '改行書込 SWrt.Write(vbCrLf) End While End If 'CSVファイルクローズ SWrt.Close() 'クローズ Conn.Close() End Sub ''' <summary>文字列をダブルクォートで囲む</summary> Private Function SuroundDblQuotes(ByVal str As String) As String If str.IndexOf(""""c) > -1 Then '文字列をダブルクォートをダブルクォートでエスケープ str = str.Replace("""", """""") End If Return """" & str & """" End Function
尚、今回のテーブルは以下の生成文で生成しました。
CREATE TABLE TEST1 ( CODE VARCHAR(8) NOT NULL ,DATA1 VARCHAR(16) ,DATA2 VARCHAR(16) ,DATA3 VARCHAR(16) ,CONSTRAINT TEST1_PKC PRIMARY KEY (CODE) ); INSERT INTO TEST1 VALUES('00000001', 'A000001', 'BBBBBB1', 'C001'); INSERT INTO TEST1 VALUES('00000002', 'A000002', 'BBBBBB2', 'C002'); INSERT INTO TEST1 VALUES('00000003', 'A000003', 'BBBBBB3', 'C003'); INSERT INTO TEST1 VALUES('00000004', 'A000004', 'BBBBBB4', 'C004');
結果的にCSVファイル内容は以下の様になります。
"CODE","DATA1","DATA2","DATA3" "00000001","A000001","BBBBBB1","C001" "00000002","A000002","BBBBBB2","C002" "00000003","A000003","BBBBBB3","C003" "00000004","A000004","BBBBBB4","C004"

■関連記事
⇒BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
⇒ADO.NET での DataReader の入れ子エラー
⇒ADO.NET を使用した SQL-Server へのアクセス・クラス
⇒SQL-Serverの自動採番(IDENTITY値)の取得・リセット
⇒SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行

以前の記事の中で「SQL Server」の「BCP」コマンドを取り上げましたが、 その時のコマンドは以下の様になっていました。 このコマンドは出力されるファイルが文字形式として書き込まれます。 （データベースの中のネイティブ形式ではないということです） bcp TEST.dbo.TABLE_A out "table_a.txt" -c -Slocalhost\SQLEXPRESS -T このコマンドで出力されるテキストファイルのコードは「Shift-JIS」形式になるはずです。
これに対応するインポートコマンドは同様に「-c」オプションをつけて以下の様になります。 bcp TEST.dbo.TABLE_A in "table_a.txt" -c -Slocalhost\SQLEXPRESS -T 今回はUnicodeでのインポート・エクスポートの必要がありましたので、「-c」オプションを「-w」に変えて行います。
以下はエキスポートコマンドです。 bcp TEST.dbo.TABLE_A out "table_a.txt" -w -Slocalhost\SQLEXPRESS -T このコマンドで出力されるテキストファイルのコードがUnicodeとなります。
以下はインポートコマンドです。 bcp TEST.dbo.TABLE_A in "table_a.txt" -w -Slocalhost\SQLEXPRESS -T

■関連記事
⇒VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
⇒ADO.NET での DataReader の入れ子エラー
⇒ADO.NET を使用した SQL-Server へのアクセス・クラス
⇒SQL-Serverの自動採番(IDENTITY値)の取得・リセット
⇒SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行

達人に学ぶDB設計 徹底指南書【電子書籍】[ ミック ] 価格： 2,808円 （2016/09/29 13:53時点 ） 感想：1件

仕事で使うことがあって、久々にIISなどというものをWindows7でインストールしてみました。
まず最初に、IISそのものをインストールします。

■IISをコントロールパネルから有効にする

１．コントロールパネルの「プログラム」をクリックします。

２．「Windows の機能の有効化または無効化」をクリックします。

３．「Windows の機能」ウィンドウが表示されたら、
　　「インターネット インフォメーション サービス」にチェックをつけ、[OK] をクリックします。
　　このとき「ASP.NET」の機能が必要だったので、「インターネット インフォメーション サービス」内の
　　「World Wide Web サービス」⇒「アプリケーション開発機能」⇒「ASP.NET」にチェックを入れました。
　　その他、必要な機能が在れば該当するところにチェックを入れます。
　　この機能の有効化には結構時間が掛かりますので、暫く待ちます。

４．インストールが終わったら、ブラウザを立ち上げてURLに「http://localhost/」と入力します。
　　インストールに問題が無ければ、以下のページが表示されるはずです。

■ASP.NETで作成したプログラムをIISに登録する

１．スタートメニューから「管理ツール」の「インターネット インフォメーション サービス(IIS)マネージャー」をクリックする。

２．「サイト」フォルダ内の「Default Web Site」上で右クリックし、メニューから「アプリケーションの追加...」をクリックする。

３．「アプリケーションの追加ウインドウ」が開くので、エイリアスと物理パスを設定します。

４．「Default Web Site」の配下にエイリアスの名前ができていればOKです。

■「HTTP エラー 500.19 - Internal Server Error　ページに関連する構成データが無効であるため、要求されたページにアクセスできません」のエラー発生
IISの設定は終わったのですが、なぜかHTTPエラーの500.19が発生しました。

あるブログからの引用なのですが、以下の処理でエラーが解消されるようです。

・定義ファイル
%windir%\system32\inetsrv\config\applicationHost.config

・変更内容
<sectionGroup name="system.webServer"> セクションの以下の項目が、デフォルトでは"Deny"になっているので"Allow"に変更する。

  <section name="handlers" overrideModeDefault="Allow" />
  <section name="modules" allowDefinition="MachineToApplication" overrideModeDefault="Allow" />

この変更後、HTTPエラーは出なくなりました。


■MSSQLでのIIS用のユーザを作成する
ここまでの設定で、「http://localhost/TEST/Index.apsx」でログイン画面が表示されたのですが、ログイン処理を行うとデータベースエラーが発生しました。

MSSQLにIISから接続するユーザが設定されていない様なので、「Microsoft SQL Server Management Studio」を起動し「セキュリティ」の中の「ログイン」で「新しいログインの追加..」をクリックしIIS用のログインを追加します。

ログインの追加ウインドウ

ログインの追加後はデータベースへのアクセスが成功するはずです。

ADO.NETを利用してSQL-Serverをアクセスするプログラムを組む場合に、データ参照のために DataReader を使用します。
一連のデータ処理では DataReader からのデータ取得をループで処理しますが、そのループ内で更にマスタ等のデータを 参照したくなることはよくあります。
この場合、同じコネクションを使いたくなり DataReader の処理中に更に DataReader を入れ子で使うことになったのですが 以下のエラーが発生しました。

System.InvalidOperationException: このコマンドに関連付けられている DataReader が既に開かれています。このコマンドを最初に閉じる必要があります。

この DataReader を入れ子で使えないかと調べたら、接続文字列の中に以下の指定を行えばできることがわかりました。

MultipleActiveResultSets=True

以前、このブログの中で 「ADO.NET を使用した SQL-Server へのアクセス・クラス」を載せていますが、接続文字列は以下の様な感じがいいと思います。

Persist Security Info=false;Integrated Security=SSPI;MultipleActiveResultSets=True;Initial Catalog=[DataBase-Name];Data Source=[DataSource-Name];

■関連記事
⇒VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
⇒BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
⇒ADO.NET を使用した SQL-Server へのアクセス・クラス
⇒SQL-Serverの自動採番(IDENTITY値)の取得・リセット
⇒SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行

ADO.NET を使用した SQL-Server へのアクセス・クラスの簡単なものを作成してみました。
このクラスを元にして拡張したものを仕事でも使っています。

このクラス「clsSqlServer」には以下のメソッド・プロパティが備わっています。

メソッド・プロパティ	概要
New	コンストラクタ(引数にデータベース接続文字列を渡す)
BeginTransaction	トランザクション開始
Commit	トランザクションコミット
Rollback	トランザクションロールバック
CloseConnection	コネクションの解除
OpenDataReader	SELECT文SQLの実行とDataReaderへの読込
CloseDataReader	DataReaderのクローズ
ExeSQL	DML-SQL文の実行
CnvReaderToHashtable	DataReaderの結果ItemsをHashtableに変換

今回のクラスのテストプログラムを以下に載せますが、SQLサーバへの接続文字列は以下の様にしています。

キーワード	概要(設定値)
Persist Security Info	ID やパスワードなどのセキュリティ関連情報の破棄指定(False)
Integrated Security	現在のWindowsアカウントでの認証(SSPI)
Database	データベース名(取敢えず「TEST」)
Data Source	SQL Serverのインスタンスの名前 (Microsoft SQL Server Management Studio Express：.\SQLEXPRESS)

このテストは、システム日付をSELECTで実行し、SqlDataReaderに取得しています。SqlDataReaderから日付を取り出して その後、クラスのクローズを行っています。

■「clsSqlServer」の使用テストソース１
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 'SQL-Server接続クラス生成 Dim DBConnection As String = "Persist Security Info=false;" & _ "Integrated Security=SSPI;Database=TEST;Data Source=.\SQLEXPRESS;" Dim CSQL = New clsSqlServer(DBConnection) 'TESTデータ取得クラス生成 Dim SQL As String = "SELECT GETDATE() AS SYSDATE" Dim Reader As System.Data.SqlClient.SqlDataReader = Nothing If CSQL.OpenDataReader(SQL, Reader) = True Then '読込 While Reader.Read() = True '読込が行われた場合,データをHashtableに格納 Dim ht As Hashtable = CSQL.CnvReaderToHashtable(Reader) MsgBox(ht("SYSDATE") & " : " & Reader.Item("SYSDATE")) End While End If 'クローズ CSQL.CloseConnection() End Sub

以下に、clsSqlServerの全体のソースを載せます。バグがあるかもしれませんが悪しからず。

■「clsSqlServer」のソース
Imports System.Data.SqlClient ''' ====================================================================== ''' <summary> ''' SQLServer処理用クラス ''' </summary> ''' ====================================================================== Class clsSqlServer 'コネクションを共有変数で持つ Private _Conn As SqlConnection = Nothing 'トランザクションを共有変数で持つ Private _Trans As SqlTransaction = Nothing '接続文字列 Private _DBConnection As String '最後の実行SQL文および、エラーメッセージ Private _LastSQL As String = "" Private _LastErr As String = "" ' -------------------------------------------------------------------- ' プロパティ ' -------------------------------------------------------------------- ''' ------------------------------------------------------------------- ''' <summary>最終実行SQL文</summary> ''' ------------------------------------------------------------------- Public ReadOnly Property LastSQL() As String Get Return Me._LastSQL End Get End Property ''' ------------------------------------------------------------------- ''' <summary>最終エラーメッセージ</summary> ''' ------------------------------------------------------------------- Public ReadOnly Property LastErr() As String Get Return Me._LastErr End Get End Property ''' ------------------------------------------------------------------- ''' <summary>コンストラクタ</summary> ''' <param name="DBConnection">データベース接続文字列</param> ''' ------------------------------------------------------------------- Public Sub New(ByVal DBConnection As String) 'データベース接続文字列の退避 Me._DBConnection = DBConnection End Sub ''' ------------------------------------------------------------------- ''' <summary> ''' トランザクション開始 ''' </summary> ''' <returns>処理結果(true:OK, false:NG)</returns> ''' ------------------------------------------------------------------- Public Function BeginTransaction() As Boolean Try _Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted) Return True Catch ex As Exception Me._LastErr = ex.ToString() _Trans = Nothing Return False End Try End Function ''' ------------------------------------------------------------------- ''' <summary> ''' トランザクションコミット ''' </summary> ''' ------------------------------------------------------------------- Public Sub Commit() Try If (_Trans IsNot Nothing) Then _Trans.Commit() _Trans = Nothing End If Catch ex As Exception Me._LastErr = ex.ToString() _Trans = Nothing End Try End Sub ''' ------------------------------------------------------------------- ''' <summary> ''' ロールバック ''' </summary> ''' ------------------------------------------------------------------- Public Sub Rollback() Try If (_Trans IsNot Nothing) Then _Trans.Rollback() _Trans = Nothing End If Catch ex As Exception Me._LastErr = ex.ToString() _Trans = Nothing End Try End Sub ''' ------------------------------------------------------------------- ''' <summary> ''' コネクションの解除 ''' </summary> ''' ------------------------------------------------------------------- Public Sub CloseConnection() Try If (_Conn IsNot Nothing) Then _Conn.Close() _Conn = Nothing End If Catch ex As Exception Me._LastErr = ex.ToString() _Conn = Nothing End Try End Sub ''' ------------------------------------------------------------------- ''' <summary> ''' SELECT文SQLの実行とDataReaderへの読込 ''' </summary> ''' <param name="strSQL">SELECT文SQL</param> ''' <param name="Reader">DataReaderへの参照</param> ''' <param name="fSingleRow">1行のみの読込指定(true:1行のみ, false:複数行)</param> ''' <returns></returns> ''' ------------------------------------------------------------------- Public Function OpenDataReader(ByVal strSQL As String, ByRef Reader As SqlDataReader, _ Optional ByVal fSingleRow As Boolean = False) As Boolean Dim functionReturnValue As Boolean = False Try '戻り値初期化 functionReturnValue = False 'DB接続処理 If _Conn Is Nothing Then '未接続の場合に接続する _Conn = New SqlConnection(Me._DBConnection) _Conn.Open() End If 'トランザクションの指定がされていればコマンドオブジェクトに関連付け Dim pCmd As SqlCommand = Nothing If (_Trans IsNot Nothing) Then pCmd = New SqlCommand(strSQL, _Conn, _Trans) Else pCmd = New SqlCommand(strSQL, _Conn) End If _LastSQL = strSQL 'SQL文の退避 '---読込オブジェクトに接続--- '強制的に1行のみ読込?? If fSingleRow = True Then Reader = pCmd.ExecuteReader(CommandBehavior.SingleRow) '行数の有無をチェック If Reader.HasRows Then If Reader.Read() Then '戻り値初期化 functionReturnValue = True End If End If Else '複数行の読込の場合はRead()関数はコールしない(この関数の外側でRead()すること) Reader = pCmd.ExecuteReader() '行数の有無をチェック If Reader.HasRows Then '戻り値初期化 functionReturnValue = True End If End If Catch ex As Exception 'エラーの退避 _LastErr = ex.Message '戻り値エラー functionReturnValue = False End Try Return functionReturnValue End Function ''' ------------------------------------------------------------------- ''' <summary> ''' DataReaderのクローズ ''' </summary> ''' <param name="Reader">DataReaderへの参照</param> ''' ------------------------------------------------------------------- Public Sub CloseDataReader(ByRef Reader As SqlDataReader) Try If (Reader IsNot Nothing) Then Reader.Close() Reader = Nothing End If Catch Reader = Nothing End Try End Sub ''' ------------------------------------------------------------------- ''' <summary> ''' DML-SQL文の実行 ''' </summary> ''' <param name="strSQL">DML-SQL文</param> ''' <returns>影響を受けた行数(-1:エラー戻り)</returns> ''' ------------------------------------------------------------------- Public Function ExeSQL(ByVal strSQL As String) As Integer Dim functionReturnValue As Integer = 0 Try '戻り値初期化 functionReturnValue = 0 'DB接続処理 If _Conn Is Nothing Then _Conn = New SqlConnection(Me._DBConnection) _Conn.Open() End If 'コマンドオブジェクト処理 Dim pCmd As New SqlCommand(strSQL, _Conn) 'SQL文の退避 _LastSQL = strSQL 'トランザクションの指定がされていればコマンドオブジェクトに関連付け If (_Trans IsNot Nothing) Then pCmd.Transaction = _Trans End If 'SQL実行 functionReturnValue = pCmd.ExecuteNonQuery() '戻り値は処理件数 'オブジェクトの廃棄 pCmd = Nothing Catch ex As Exception 'エラーの退避 _LastErr = ex.Message 'エラーNo取得 functionReturnValue = -1 End Try Return functionReturnValue End Function ''' ------------------------------------------------------------------- ''' <summary> ''' DataReaderの結果ItemsをHashtableに変換 ''' </summary> ''' <param name="Reader">DataReader</param> ''' <returns>Hashtable</returns> ''' ------------------------------------------------------------------- Public Function CnvReaderToHashtable(ByVal Reader As SqlDataReader) As Hashtable 'HashTableの生成 Dim ht As New Hashtable() If Reader Is Nothing Then '引数チェックでNULLの場合 Return ht End If If Reader.IsClosed Then 'クローズされている場合 Return ht End If Try 'カラム名 Dim columnName As String = "" 'フィールド数の処理 For i As Integer = 0 To Reader.FieldCount - 1 'カラム名 columnName = Reader.GetName(i) 'HashTableへのReaderの内容Objectを追加 ht.Add(columnName, Reader.GetValue(i)) Next Catch Return ht End Try Return ht End Function End Class

■関連記事
⇒VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
⇒BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
⇒ADO.NET での DataReader の入れ子エラー
⇒SQL-Serverの自動採番(IDENTITY値)の取得・リセット
⇒SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行

システムに必要なテーブルで、自動的に番号を振っていくものが必要なときがあります。 たとえば、各種の伝票データの伝票番号の様なものです。

プログラム処理上、データを登録した直後に、自動採番された値を取得し何かに使いたいことがよくあります。
SQL-Serverでは現在の自動採番の値を取得する方法が用意されています。

取敢えず自動採番を行える簡単なテーブルの例を示します。（前回ストアド・ファンクションで使用したテーブルに細工します。）

CREATE TABLE [dbo].[TABLE_B]( [ID] [int] IDENTITY(1,1) NOT NULL, [DATA1] [nvarchar](50) COLLATE Japanese_CI_AS NULL, [DATA2] [nvarchar](50) COLLATE Japanese_CI_AS NULL, CONSTRAINT [PKEY_TW_MAG_ADDR] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

まず、テストテーブルＢに1件データを挿入します。 「ID」は自動採番の設定なので登録する値は設定しません。
INSERT INTO TABLE_B (DATA1, DATA2) VALUES('A0001', 'B0001') ■自動採番された値を取得
この直後に、設定された「ID」値を取得するには以下のSELECT文を実行します。
SELECT IDENT_CURRENT('TABLE_B') AS LASTID 結果として「１」という値が返されます。

■自動採番された値をリセット
デバッグ途中でテーブルのデータを全て削除して、さらに自動採番が「１」からにしたい場合はよくあります。 そのときに以下の命令をクエリアナライザ等で実行します。
DBCC CHECKIDENT('TABLE_B', RESEED, 0) 自動採番の値を「０」にする命令ですが、実際INSERT実行時には＋１された値が「ID」に設定されます。

■関連記事
⇒VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
⇒BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
⇒ADO.NET での DataReader の入れ子エラー
⇒ADO.NET を使用した SQL-Server へのアクセス・クラス
⇒SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行

当然といえばそうなのかもしれませんが、SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない。
TRY・・・CATCHも記述できないようです。関数は戻り値のみを返すもので、テーブル等に変更を与えてはいけないのでしょう。

さて、取敢えずストアド・ファンクションの簡単な例を示します。

前回使用したテストテーブルを利用して、ストアド・ファンクションを作成します。テーブルの構造は以下の感じです。 CREATE TABLE [dbo].[TABLE_A]( [ID] [int] NOT NULL, [DATA1] [nvarchar](50) COLLATE Japanese_CI_AS NULL, [DATA2] [nvarchar](50) COLLATE Japanese_CI_AS NULL, CONSTRAINT [PK_TABLE_A] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
■スカラ値関数

テストテーブルＡを利用した簡単な値を返すストアド・関数です。
与えられた検索用ＩＤでテーブルＡの「DATA1」カラムの内容を返す簡単な関数です。 IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL     DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2](   @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN     DECLARE @Des NVARCHAR(50)     SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID     RETURN @Des END SELECT文での実行は以下のようにします。
======================================
SELECT [dbo].[FuncTest2](1)
======================================

■テーブル値関数その１（インラインテーブル値）

テストテーブルＡを利用した簡単な値を返すストアド・関数です。 IF OBJECT_ID('dbo.FuncTest3') IS NOT NULL     DROP FUNCTION [dbo].[FuncTest3] GO CREATE FUNCTION [dbo].[FuncTest3](   @SrcID NVARCHAR ) RETURNS TABLE AS RETURN (     SELECT ID, DATA1 FROM TABLE_A WHERE ID = @SrcID )
SELECT文のFROM句で実行しますので、以下のようになります。
======================================
SELECT * FROM [dbo].[FuncTest3](1)
======================================

■テーブル値関数その２（複数の行を返すテーブル値）

テストテーブルＡを利用した複数の行を返すストアド・関数です。
指定されたID以降のデータを全て返す様な動作を行います。
RETURNS句で指定されたTABLEにINSERTしていくことで、結果をテーブルの値として返します。
IF OBJECT_ID('dbo.FuncTest4') IS NOT NULL     DROP FUNCTION [dbo].[FuncTest4] GO CREATE FUNCTION [dbo].[FuncTest4]( @SrcID NVARCHAR ) RETURNS @DesTbl TABLE (     ID int primary key NOT NULL,     DATA1 nvarchar(255) NOT NULL ) AS BEGIN INSERT @DesTbl SELECT ID, LTRIM( DATA1 ) FROM TABLE_A WHERE ID >= @SrcID /* INSERT @DesTbl VALUES(0, '0000') */ RETURN END INSERTで追加してやればいろんなことができると思います。上の例ではコメント行になっていますが、 強制的にID「０」を作成することも可能です。
この関数の実行も「テーブル値関数その１」で行った方法と同様です。


■スカラ値関数にUPDATE文を記述
IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL     DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2](   @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN     DECLARE @Des NVARCHAR(50)     SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID UPDATE TABLE_A SET DATA2 = NULL WHERE ID = @SrcID     RETURN @Des END SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 15、プロシージャ FuncTest2、行 8
副作用のある演算子または時間に依存する演算子を関数内の 'UPDATE' で使用することはできません。
============================================================

TRY、CATCHを入れてもエラーが表示され使用出来ないようです。
IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL     DROP FUNCTION [dbo].[FuncTest2] GO CREATE FUNCTION [dbo].[FuncTest2](   @SrcID NVARCHAR ) RETURNS NVARCHAR(50) BEGIN BEGIN TRY     DECLARE @Des NVARCHAR(50)     SELECT @Des = DATA1 FROM TABLE_A WHERE ID = @SrcID END TRY BEGIN CATCH END CATCH     RETURN @Des END SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 6
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 9
副作用のある演算子または時間に依存する演算子を関数内の 'END TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 10
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN CATCH' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 11
副作用のある演算子または時間に依存する演算子を関数内の 'END CATCH' で使用することはできません。
============================================================


副作用のある処理をファンクションのなかでは記述できないので、 どうしても処理したいのであれば、ストアド・プロシージャとして作成するしかないようです。
そこで、結果として何かを返したいのであれば、引数に出力属性のものを持つしかありません。

また、プロシージャの中からファンクションを実行することはできますが、ファンクションの中から プロシージャを実行することはできないようです。
ファンクションのコールが入れ子になっていて、深いところでUPDATEなどを行いたい場合、 全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。

■関連記事
⇒VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
⇒BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
⇒ADO.NET での DataReader の入れ子エラー
⇒ADO.NET を使用した SQL-Server へのアクセス・クラス
⇒SQL-Serverの自動採番(IDENTITY値)の取得・リセット
⇒SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
⇒SQL-Serverのプロシージャ生成のバッチ実行
⇒SQL-Serverの関数・プロシージャのVB.NETでの実行