sql server中存儲過程的寫法格式規格

 

/*****************************************************

*** author：Susan

*** date:2005/08/05

*** expliation:如何寫存儲過程的格式及例子，有游標的用法！

*** 本版:SQL SERVER 版！

******************************************************/

在存儲過程中的格式規格：

CREATE PROCEDURE XXX

/*

列舉傳入參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE varCHAR(3)

*/

參數1，

參數2……………

As

/*

定義內部參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE varCHAR(3)

*/

Declare

參數1，

參數2……………

/*

初始化內部參數

Eg:SET @strUNIT_CODE=’’

*/

Set參數1的初始值

Set參數2的初始值…………

/*

過程的主內容區

Trascation：這裡起到的作用是，如果他中間的任何一個執行錯誤，就全部執行都返回，這裡sql sever 7.0以前一定要寫入，以後的就可以省略

Return：結束這支sp

*/

Begin trascation

    /*

        1:可以取得需要的值以存在內部參數中

     Eg:SELECT @strUNIT_CODE=UNIT_CODE FROM UNIT WHERE …….

2:可以用取到的或傳入的參數進行判斷，來進行update,insert,delete 等等操作

eg: IF @strUNIT_CODE=’’

         BEGIN

     //具體的操作

End

Else

Begin

    //具體的操作

End

 3：有關游標的問題

     Eg:

        declare db cursor for       //聲明一個游標(db為其名稱)

        SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@strTO,2)//記錄集

        open db                 //打開游標

            fetch next from db into @strUNIT_NAME //將第一個值放入一個參數中

        while @@fetch_status = 0 ---存在本筆值向下循環

(0：順利執行;-1：失敗，或資料列超出結果集;-2：擷取的資料列已遺漏)

        BEGIN             ----開始循環

                        //個體操作

        End                ----結束循環

        Close db            ---關閉游標

deallocate db         //移除資料指標參考

*/

Commit trascation

Return

 

下面是一個例子

CREATE PROCEDURE TEST_2

@strTO VARCHAR(3)

AS

DECLARE

 @strUNIT_NAME VARCHAR(800),

 @strSQL VARCHAR(8000),

 @Link VARCHAR(1),

 @Link1 VARCHAR(1)

 

SET @strUNIT_NAME=''

SET @strSQL=''

SET @Link=''

SET @Link1=''

 

/*

處理update 的部分

EXEC TEST_2 '011'

EXEC TEST_2 ''

SELECT UNIT_NAME FROM UNIT WHERE UNIT_CODE='011'

*/

BEGIN TRANSACTION                                       

         IF @strTO<>''

        BEGIN

                UPDATE UNIT SET UNIT_NAME=REPLACE(UNIT_NAME,'*','') WHERE UNIT_CODE=@strTO

        END

             ELSE

        BEGIN

                UPDATE UNIT SET UNIT_NAME=UNIT_NAME+'*' WHERE UNIT_CODE='011'

        END

/*

EXEC TEST_2 '011'

功能說明：本sp用於處理cursor問題

*/

        IF   @strTO<>''

            BEGIN

                        declare db cursor for                                                --必需聲明在查詢的前面

                SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@strTO,2)---取到相關信息

            END

        ELSE

            BEGIN

                     declare db cursor for                                              --必需聲明在查詢的前面

                     SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT('011',2)---取到相關信息

            END

        open db                                                    ---開起取到的信息

        fetch next from db into @strUNIT_NAME        ---把第一筆放入@strUNIT_NAME中

        while @@fetch_status = 0                                           ---表示存在本筆資料

        BEGIN                                                    ----開始循環

                set @strSQL =@strSQL+@Link1+@Link+ @strUNIT_NAME ----設定保存的值

                fetch next from db into @strUNIT_NAME                       ----進行下次循環

                SET @Link=CHAR(13) +CHAR(10)

                SET @Link1=','

        END                                                        ----結束循環

        close db                                                   ---關閉信息

        deallocate db                                            ---移除資料指標參考

SELECT @strSQL

COMMIT TRANSACTION

RETURN