ストアドの色々

SQL

■引数を生成

・Create句の直後に記述

CREATE PROCEDURE [dbo].spStudy2 

　@Master NVARCHAR(100)= NULL 　　　 '引数

　,@ReturnValue NVARCHAR(100) OUTPUT '戻り値
AS

 

■IF文

・ELSE IF句はそもそもない

・IF文内の適用範囲(IFはどこまでか、ELSE IFはどこまでか)はBEGIN-ENDで設定

・IFやELSEの次の処理が1行だけなら、BEGIN,ENDで囲まなくともよい

BEGIN

　IF @Master IS NULL

　　BEGIN 

　　　SELECT * FROM EnumMaster

　　END

　ELSE IF (SELECT COUNT(*) FROM EnumMaster WHERE MasterType = @Master ) = 0

　　BEGIN

　　　PRINT '対象データがありません'
　　END
　ELSE

　　BEGIN

　　　PRINT @Master

　　　SELECT * FROM EnumMaster WHERE MasterType = @Master

　　END

END

■WHILE文

WHILE　@@FETCH_STATUS = 0

BEGIN

　INSERT INTO #MType INSERT INTO #MType SELECT 　@Counter,@MasterType,@MasterText

END 

■ローカル変数宣言

　DECLARE @MasterType NVARCHAR(100) = '初期値' 

■SELECT結果を変数に格納

　SELECT @ReturnValue = UserName FROM wrk  WHERE UserId = '11111'

■テーブル型変数

　・テーブルの様な変数を定義できる。

　DECLARE @TestTable TABLE( 

　　UserId INT NOT NULL,

　　UserName NVARCHAR(100)

　)

　普通のテーブルと同じようにSELECTやDELETE、INSERTが可能

■一時テーブル

#一つでローカル一時テーブル

#二つでグローバル一時テーブル

 CREATE TABLE #MType(

　RowCounts INT PRIMARY KEY,

　TypeName NVARCHAR(100)

)

グローバル： CREATE TABLE ##MType(

 

■実行メッセージを非表示にする

SET NOCOUNT ON

初期化処理として記載されるのが一般的

 

■カーソル

・カーソルを宣言

　DECLARE MasterCursor CURSOR FOR 

　　SELECT MasterType,MasterText FROM EnumMaster

・カーソルをオープン

　OPEN MasterCursor

・カーソルを次の行に進め行の値を変数にセットする

　FETCH NEXT FROM MasterCursor

　INTO @MasterType,@MasterText

・カーソルをループ

　@@FETCH_STATUSで現在のFETCH状況を取得できる。

　先頭行FETCH前または最終行FETCH後は、-1になる。

　それを利用し、WHILEループで0の間回す

　WHILE　@@FETCH_STATUS = 0

　BEGIN

　　FETCH NEXT FROM MasterCursor

　　INTO @MasterType,@MasterText

　END

・カーソルをクローズ

　CLOSE MasterCursor

・カーソルを解放

　DEALLOCATE MasterCursor

・一連の流れをセットで書くと

DECLARE MasterCursor CURSOR FOR

　SELECT MasterType,MasterText FROM EnumMaste

OPEN MasterCursor

FETCH NEXT FROM MasterCursor

　INTO @MasterType,@MasterText

WHILE @@FETCH_STATUS = 0

　BEGIN

　　INSERT INTO #MType SELECT @Counter,@MasterType,@MasterText

　　　SET @Counter = @Counter +1

　　FETCH NEXT FROM MasterCursor INTO @MasterType,@MasterText

　END

CLOSE MasterCursor

DEALLOCATE MasterCursor

■例外処理

・BEGIN TRY～END TYRで正常処理を記載

・BEGIN CATCH～END CATCHでエラーじ処理を記載

　BEGIN TRY

　　BEGIN TRAN

　　　INSERT INTO wrk(UserId) VALUES('1')

        COMMIT

　END TRY

　BEGIN CATCH 　

　　@ExecuteStatus = @@ERROR

　　/* トランザクションがある場合、ROLLBACKする            */

　　IF @@TRANCOUNT > 0

　　　ROLLBACK

　END CATCH