MS SQL Server儲存過程

libingql發表於2014-03-01
SQLServer儲存過程

1、Create、Alter和Drop

CREATE PROCEDURE USP_CategoryList
AS
    SELECT CategoryID,CategoryName
    FROM Category

　　在建立儲存過程時，應該使用一致的規範來命名儲存過程，而不要使用sp_，使用sp_只會導致名稱衝突和混亂。可以使用usp_作為儲存過程名的字首，但即使不使用任何字首，也比使用sp_好。

2、返回記錄集

EXEC USP_CategoryList

3、編譯儲存過程

EXEC sp_recompile USP_CategoryList

4、檢視儲存過程

sp_helptext USP_CategoryList

5、加密儲存過程

CREATE PROCEDURE USP_CategoryList
    WITH ENCRYPTION
AS
    SELECT CategoryID,CategoryName
    FROM Category

6、輸入引數

CREATE PROCEDURE USP_CategoryList
(
    @CategoryID INT
)
AS
    SELECT CategoryID,CategoryName
    FROM Category
    WHERE CategoryID = @CategoryID
EXEC USP_CategoryList 1
EXEC StoredProcedure
    @Parameter1 = n,
    @Parameter2 = 'n'

7、引數的預設值

　　呼叫儲存過程時，必須為所有引數指定值，除非建立引數有預設值。

CREATE PROCEDURE StoredProcedure
(
    @Variable DataType = DefaultValue       
)

8、輸出引數

　　使用output定義輸出引數，在不需要返回整個記錄集時，輸出引數非常適合用於返回單個資料單元。返回單行資料時，使用輸出引數比使用記錄集快得多。

CREATE PROCEDURE USP_CategoryList
(
    @CategoryID INT,
    @CategoryName VARCHAR(50) OUTPUT
)
AS
    SELECT @CategoryName = CategoryName
    FROM Category
    WHERE CategoryID = @CategoryID
DECLARE @CategoryName VARCHAR(50)
EXEC USP_CategoryList @CategoryID = 1, @CategoryName OUTPUT
PRINT @CategoryName

9、return關鍵字

　　return關鍵字無條件地終止儲存過程的執行，並向呼叫它的批處理或客戶返回一個值。

　　使用返回值來指出執行是失敗還是成功，而不是使用它返回實際資料。如果只需要返回一個值而不是這個資料集，可以使用輸出引數。

EXEC @IntLocalVariable = storedProcedureName
CREATE PROCEDURE USP_IsItOK
(
    @OK VARCHAR(10)   
)
AS
    IF @OK = 'OK'
         RETURN 0
    ELSE
         RETURN -100
DECLARE @ReturnCode INT
EXEC @ReturnCode = USP_IsItOK 'OK'
PRINT @ReturnCode
EXEC @ReturnCode = USP_IsItOK 'NotOK'
PRINT @ReturnCode

相關文章