存储过程学习（里面包含游标，事务，以及如何抛出异常等）

USE [JGB_DB]
GO
/****** 对象:  StoredProcedure [dbo].[P_CREATE_DATA_BY_EXCEL]    脚本日期: 08/13/2013 09:01:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[P_CREATE_DATA_BY_EXCEL] 
    -- Add the parameters for the stored procedure here
    @GUID_VALUE UNIQUEIDENTIFIER ,
    @RESULTMSG VARCHAR(1000) OUTPUT ,    --错误消息
    @RESULT BIT OUTPUT     --返回消息  1正确，0错误
AS 
    BEGIN
        DECLARE @BLOCKNAME VARCHAR(20) ,
            @ERROMESSAGE VARCHAR(20) ,
            @BLOCKID BIGINT ,
            @PROJ_ID BIGINT ,
            @PROJ_NAME VARCHAR(100) ,
            @I INT ,
            @G_NAME VARCHAR(400) ,
            @ERR_CODE VARCHAR(200)
        
        BEGIN TRY
            BEGIN TRANSACTION
            DECLARE CR_TOTAL CURSOR
            FOR
                SELECT  DISTINCT
                        BLOCK_NAME ,
                        G_NAME ,
                        PROJ_NAME
                FROM    dbo.T_INPOUR_DATA
                WHERE   GUID_VALUE = @GUID_VALUE
            OPEN CR_TOTAL
            FETCH NEXT FROM CR_TOTAL INTO @BLOCKNAME, @G_NAME, @PROJ_NAME
            WHILE ( @@FETCH_STATUS = 0 ) 
                BEGIN
                    SELECT  @PROJ_ID = PROJ_ID
                    FROM    dbo.T_PROJECT
                    WHERE   PROJ_NAME = @PROJ_NAME
            --获取地块ID
                    SET @ERR_CODE = '0.03'
                    SET @ERROMESSAGE = '楼盘获取失败'
                    DECLARE @GR_ID BIGINT
                    IF NOT EXISTS ( SELECT  *
                                    FROM    dbo.T_GROUND
                                    WHERE   G_NAME = @G_NAME AND PROJ_ID=@PROJ_ID ) 
                        BEGIN
                            INSERT  INTO dbo.T_GROUND
                                    ( PROJ_ID, G_NAME )
                            VALUES  ( @PROJ_ID, -- PROJ_ID - int
                                      @G_NAME )
                            SET @GR_ID = IDENT_CURRENT('T_GROUND')
                        END
                    ELSE 
                        SELECT  @GR_ID = GR_ID
                        FROM    dbo.T_GROUND
                        WHERE   G_NAME = @G_NAME
                                AND PROJ_ID = @PROJ_ID    
            --删除旧数据
                    SET @ERR_CODE = '0.01'
                    SET @ERROMESSAGE = '原楼数据删除失败'
                    IF EXISTS ( SELECT  *
                                FROM    dbo.T_BLOCK
                                WHERE   BLOCK_NAME = @BLOCKNAME AND GR_ID=@GR_ID ) 
                        BEGIN
                            SELECT  @BLOCKID = BLOCK_ID
                            FROM    dbo.T_BLOCK
                            WHERE   BLOCK_NAME = @BLOCKNAME
                                    AND GR_ID = @GR_ID
                            DELETE  FROM dbo.T_ROOM
                            WHERE   BLOCK_ID = @BLOCKID
                            DELETE  FROM T_BLOCK_LAYER
                            WHERE   BLOCK_ID = @BLOCKID
                            DELETE  FROM T_BLOCK_BRANCH
                            WHERE   BLOCK_ID = @BLOCKID
                    
                            DELETE  FROM dbo.T_BLOCK
                            WHERE   BLOCK_ID = @BLOCKID
                        END
             
             --获取楼的单元数、楼层数
                    SET @ERR_CODE = '0.02'
                    SET @ERROMESSAGE = '单元号存在不合法数据'
                    DECLARE @ROOMNO VARCHAR(20)
                    DECLARE @MAXLAYER INT ,
                        @LAYER INT
                    DECLARE @MAXBRACH INT ,
                        @BRACH INT
                    SET @MAXBRACH = 0
                    SET @MAXLAYER = 0
                    DECLARE CR_LAYER CURSOR
                    FOR
                        SELECT  ROOM_NO
                        FROM    dbo.T_INPOUR_DATA
                        WHERE   GUID_VALUE = @GUID_VALUE
                                AND PROJ_NAME = @PROJ_NAME
                                AND G_NAME = @G_NAME
                                AND BLOCK_NAME = @BLOCKNAME
                    OPEN CR_LAYER
                    FETCH NEXT FROM CR_LAYER INTO @ROOMNO
                    WHILE ( @@FETCH_STATUS = 0 ) 
                        BEGIN
                            IF LEN(@ROOMNO) < 4 
                                BEGIN
                                    SET @LAYER = CAST(SUBSTRING(@ROOMNO, 1, 1) AS INT)
                                    SET @BRACH = CAST(SUBSTRING(@ROOMNO, 2, 2) AS INT) 
                                    IF ( @LAYER > @MAXLAYER ) 
                                        SET @MAXLAYER = @LAYER
                                    IF ( @BRACH > @MAXBRACH ) 
                                        SET @MAXBRACH = @BRACH
                                END
                            ELSE 
                                BEGIN
                                    SET @LAYER = CAST(SUBSTRING(@ROOMNO, 1, 2) AS INT)
                                    SET @BRACH = CAST(SUBSTRING(@ROOMNO, 3, 2) AS INT)
                                    IF ( @LAYER > @MAXLAYER ) 
                                        SET @MAXLAYER = @LAYER
                                    IF ( @BRACH > @MAXBRACH ) 
                                        SET @MAXBRACH = @BRACH
                                END 
                               
                            FETCH NEXT FROM CR_LAYER INTO @ROOMNO
                        END
                    CLOSE CR_LAYER
                    DEALLOCATE CR_LAYER
            
             
            --生成楼
                    SET @ERR_CODE = '1.0'
                    SET @ERROMESSAGE = '生成楼失败'
                    INSERT  INTO T_BLOCK
                            ( GR_ID, BLOCK_NAME )
                    VALUES  ( @GR_ID, @BLOCKNAME )
                       
                    SET @BLOCKID = IDENT_CURRENT('T_BLOCK') --SCOPE_IDENTITY()  
                       --生成楼层
                    SET @ERR_CODE = '1.2'
                       
                    SET @I = 1
                    WHILE @I <= @MAXLAYER 
                        BEGIN
                            INSERT  INTO T_BLOCK_LAYER
                                    ( BLOCK_ID, LAYER_NUM )
                            VALUES  ( @BLOCKID, @I )
                              
                            SET @I = @I + 1
                        END
                       --生成单元
                    SET @ERR_CODE = '1.3'
                    SET @ERROMESSAGE = '生成单元失败'
                    SET @I = 1
                    WHILE @I <= @MAXBRACH 
                        BEGIN
                            INSERT  INTO T_BLOCK_BRANCH
                                    ( BLOCK_ID, BRANCH_NUM )
                            VALUES  ( @BLOCKID, @I )
                              
                            SET @I = @I + 1
                        END
            --生成房屋
                    SET @ERROMESSAGE = '生成房间失败'
                    DECLARE @ROOM_NO VARCHAR(20) ,
                        @LAYER_NUM INT ,
                        @BRACH_NUM INT ,
                        @ROOM_SHAPE INT ,
                        @ROOM_SIZE NUMERIC(10, 2) ,
                        @ROOM_SIZE_INNER NUMERIC(10, 2) ,
                        @BLOCK_LAYER_ID BIGINT ,
                        @BLOCK_BRANCH_ID BIGINT ,
                        @ROOM_SIZE_OUTER NUMERIC(10, 2)
                    SET @ERR_CODE = '1.4.1'
                    DECLARE CR_LAYER CURSOR
                    FOR
                        SELECT  ROOM_NO ,
                                ROOM_SHAPE ,
                                ROOM_SIZE ,
                                ROOM_SIZE_INNER ,
                                ROOM_SIZE_OUTER
                        FROM    dbo.T_INPOUR_DATA
                        WHERE   GUID_VALUE = @GUID_VALUE
                                AND PROJ_NAME = @PROJ_NAME
                                AND G_NAME = @G_NAME
                                AND BLOCK_NAME = @BLOCKNAME
                       
   
                    OPEN CR_LAYER
                    FETCH NEXT FROM CR_LAYER INTO @ROOM_NO, @ROOM_SHAPE,
                        @ROOM_SIZE, @ROOM_SIZE_INNER, @ROOM_SIZE_OUTER
                    WHILE ( @@FETCH_STATUS = 0 ) 
                        BEGIN
                            IF LEN(@ROOM_NO) < 4 
                                BEGIN
                                    SET @LAYER_NUM = CAST(SUBSTRING(@ROOM_NO,
                                                              1, 1) AS INT)
                                    SET @BRACH_NUM = CAST(SUBSTRING(@ROOM_NO,
                                                              2, 2) AS INT) 
                                END
                            ELSE 
                                BEGIN
                                    SET @LAYER_NUM = CAST(SUBSTRING(@ROOM_NO,
                                                              1, 2) AS INT)
                                    SET @BRACH_NUM = CAST(SUBSTRING(@ROOM_NO,
                                                              3, 2) AS INT)
                                END 
                            SELECT  @BLOCK_BRANCH_ID = BLOCK_BRANCH_ID
                            FROM    dbo.T_BLOCK_BRANCH
                            WHERE   BLOCK_ID = @BLOCKID
                                    AND BRANCH_NUM = @BRACH_NUM
                            SELECT  @BLOCK_LAYER_ID = BLOCK_LAYER_ID
                            FROM    dbo.T_BLOCK_LAYER
                            WHERE   BLOCK_ID = @BLOCKID
                                    AND LAYER_NUM = @LAYER_NUM
                            INSERT  INTO T_ROOM
                                    ( BLOCK_ID ,
                                      BLOCK_LAYER_ID ,
                                      BLOCK_BRANCH_ID ,
                                      ROOM_NO ,
                                      ROOM_SHAPE ,
                                      ROOM_SIZE ,
                                      ROOM_SIZE_INNER ,
                                      ROOM_SIZE_OUTER
                                    )
                            VALUES  ( @BLOCKID ,
                                      @BLOCK_LAYER_ID ,
                                      @BLOCK_BRANCH_ID ,
                                      @ROOM_NO ,
                                      @ROOM_SHAPE ,
                                      @ROOM_SIZE ,
                                      @ROOM_SIZE_INNER ,
                                      @ROOM_SIZE_OUTER
                                    ) 
                            FETCH NEXT FROM CR_LAYER INTO @ROOM_NO,
                                @ROOM_SHAPE, @ROOM_SIZE, @ROOM_SIZE_INNER,
                                @ROOM_SIZE_OUTER
                        END
                    CLOSE CR_LAYER
                    DEALLOCATE CR_LAYER
                    FETCH NEXT FROM CR_TOTAL INTO @BLOCKNAME, @G_NAME,
                        @PROJ_NAME
                END
            CLOSE CR_TOTAL
            DEALLOCATE CR_TOTAL 
            --删除导入dbo.T_INPOUR_DATA的数据
            DELETE  FROM dbo.T_INPOUR_DATA
            WHERE   GUID_VALUE = @GUID_VALUE
            SET @RESULT = 1
                    
            SET @RESULTMSG = '导入数据成功！'
            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            SET @RESULT = 0
            SET @RESULTMSG = '出现错误！错误信息：' + @ERROMESSAGE
            ROLLBACK TRANSACTION
            
        END CATCH 
    END

抛出异常的方式：

USE [JGB_DB]
GO
/****** 对象:  StoredProcedure [dbo].[P_GENERATE_BLOCK_DIMENSION]    脚本日期: 08/13/2013 09:02:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[P_GENERATE_BLOCK_DIMENSION]
/*增、删、改 建楼、楼层、楼单元和房间*/
    @GR_ID                BIGINT,       --地块id
    @BLOCK_ID             BIGINT,      --楼ID  "如果新建，传NULL"
    @BLOCK_NAME           varchar(30),  --楼名称
    /*@PICTURE_PATH         varchar(200), --楼总平面图文件所在路径*/
    @BLOCK_ORDER          INT,          --楼序号
    
    @LAYER_CNT            INT,          --楼层数
    @BRANCH_CNT           INT,          --单元数
    
    @OPER_TYPE            VARCHAR(2),   --操作类型 "增，删，替"
    
    @NEW_BLOCK_ID        BIGINT        OUTPUT,    --返回的block_id，如果删除和异常返回 null ,替不变，改，增，为新id
    @RESULTMSG          VARCHAR(1000) OUTPUT,    --错误消息
    @RESULT               BIT           OUTPUT     --返回消息  1正确，0错误

AS
BEGIN
        DECLARE 
                @I  INT, @J INT,
                @BLOCK_LAYER_ID BIGINT,@LAYER_NUM INT,
                @BLOCK_BRANCH_ID BIGINT,@BRANCH_NUM INT,
                @LAYER_NO VARCHAR(2),@BRANCH_NO VARCHAR(2)
                
        
        DECLARE @POS_FN INT, @POS_FV INT, @POS_PR INT --临时变量用于存储,的位置
        DECLARE @CHOOSEROOM_FAMILY_CNT INT
        DECLARE @ERR_CODE VARCHAR(200)
        
        BEGIN TRY
            BEGIN TRANSACTION
               --查询是否选择
               IF (@BLOCK_ID is not null)--(@OPER_TYPE IN ('改','删','替'))  --改 或 删，替
               BEGIN                    
                       SELECT @CHOOSEROOM_FAMILY_CNT = COUNT(ROOM_ID) FROM T_ROOM WHERE BLOCK_ID = @BLOCK_ID AND SETTLE_ID IS NOT NULL
                       IF @CHOOSEROOM_FAMILY_CNT = 0 
                       BEGIN
                           IF @OPER_TYPE = '替'
                           BEGIN
                               GOTO Branch_Generate
                           END
                        ELSE 
                        BEGIN                        
                            
                               --删除房间、楼层、单元、楼
                               DELETE FROM T_ROOM           WHERE BLOCK_ID = @BLOCK_ID
                               DELETE FROM T_BLOCK_LAYER  WHERE BLOCK_ID = @BLOCK_ID
                               DELETE FROM T_BLOCK_BRANCH WHERE BLOCK_ID = @BLOCK_ID
                               DELETE FROM T_BLOCK           WHERE BLOCK_ID = @BLOCK_ID
                               
                               IF @OPER_TYPE = '删' 
                                GOTO Branch_DELOK
                               ELSE -- 改                                
                                   GOTO Branch_Generate

                        END
                           

                       END
                       ELSE  --房源已被选
                          GOTO Branch_Donothing    
               END
               ELSE --增
               BEGIN
                       GOTO Branch_Generate
               END
             
              Branch_Generate: -- 增，替 和 改
               
               IF @OPER_TYPE = '替' 
               BEGIN
 
                         UPDATE T_BLOCK
                         SET GR_ID = @GR_ID
                             ,BLOCK_NAME = @BLOCK_NAME
                             /*,PICTURE_PATH = @PICTURE_PATH*/
                             ,BLOCK_ORDER = @BLOCK_ORDER
                         WHERE BLOCK_ID=@BLOCK_ID
                         
                      

                         SET @RESULT = 1
                      SET @NEW_BLOCK_ID = @BLOCK_ID
                      SET @RESULTMSG = @OPER_TYPE + '成功！'
                      COMMIT TRANSACTION
                    
                      Goto Branch_End
               END
               ELSE --增　和 改
               BEGIN
                          --生成楼
                          SET @ERR_CODE = '1.0'
                       INSERT INTO T_BLOCK(GR_ID,BLOCK_NAME,BLOCK_ORDER)
                       VALUES(@GR_ID,@BLOCK_NAME,@BLOCK_ORDER)
                       
                       SET @BLOCK_ID = IDENT_CURRENT('T_BLOCK') --SCOPE_IDENTITY()  
                       --生成楼层
                       SET @ERR_CODE = '1.2'
                       
                       SET @I=1
                       WHILE @I <= @LAYER_CNT
                       BEGIN
                              INSERT INTO T_BLOCK_LAYER(BLOCK_ID,LAYER_NUM)
                              VALUES(@BLOCK_ID,@I)
                              
                              SET @I = @I + 1
                       END
                       --生成单元
                       SET @ERR_CODE = '1.3'
                       SET @I=1
                       WHILE @I <= @BRANCH_CNT
                       BEGIN
                              INSERT INTO T_BLOCK_BRANCH(BLOCK_ID,BRANCH_NUM)
                              VALUES(@BLOCK_ID,@I)
                              
                              SET @I = @I + 1
                       END
                       
                        --生成房屋
                        
                        SET @ERR_CODE = '1.4.1'

                      DECLARE CR_LAYER CURSOR FOR 
                        SELECT BLOCK_LAYER_ID,LAYER_NUM FROM T_BLOCK_LAYER 
                        WHERE BLOCK_ID=@BLOCK_ID
                        ORDER BY LAYER_NUM    
                       
   
                        OPEN CR_LAYER
                      
                       
                       FETCH NEXT FROM CR_LAYER INTO @BLOCK_LAYER_ID,@LAYER_NUM

                       WHILE (@@FETCH_STATUS = 0)
                       BEGIN
                                 SET @ERR_CODE = '1.4.3.0'
                                --IF @LAYER_NUM < 10
                                   --SET @LAYER_NO = '0' + CONVERT(VARCHAR(1),@LAYER_NUM)
                                --ELSE 
                                    SET @LAYER_NO = CONVERT(VARCHAR(2),@LAYER_NUM)
                                
                                --游标要临时创建    
                                 DECLARE CR_BRANCH CURSOR FOR 
                             SELECT BLOCK_BRANCH_ID,BRANCH_NUM FROM T_BLOCK_BRANCH 
                             WHERE BLOCK_ID=@BLOCK_ID
                             ORDER BY BRANCH_NUM
                                
                                OPEN CR_BRANCH
                             FETCH NEXT FROM CR_BRANCH INTO @BLOCK_BRANCH_ID,@BRANCH_NUM
                             WHILE (@@FETCH_STATUS = 0)
                             BEGIN
                                 IF @BRANCH_NUM<10
                                       SET @BRANCH_NO = '0' + CONVERT(VARCHAR(1),@BRANCH_NUM)
                                    ELSE 
                                        SET @BRANCH_NO = CONVERT(VARCHAR(2),@BRANCH_NUM)
                                
                                INSERT INTO T_ROOM(BLOCK_ID,BLOCK_LAYER_ID,BLOCK_BRANCH_ID,ROOM_NO)
                                VALUES(@BLOCK_ID,@BLOCK_LAYER_ID,@BLOCK_BRANCH_ID,@LAYER_NO + @BRANCH_NO)
                          
                                 
                                   FETCH NEXT FROM CR_BRANCH INTO @BLOCK_BRANCH_ID,@BRANCH_NUM
                             END
                            CLOSE CR_BRANCH
                            DEALLOCATE CR_BRANCH    
                                                       
                               FETCH NEXT FROM CR_LAYER INTO @BLOCK_LAYER_ID,@LAYER_NUM
                       END
                       
                       CLOSE CR_LAYER
                       DEALLOCATE CR_LAYER
                       
                    SET @NEW_BLOCK_ID = @BLOCK_ID
                    SET @RESULT = 1
                    
                    SET @RESULTMSG = @OPER_TYPE + '成功！' +  isnull(@ERR_CODE ,'')
                    COMMIT TRANSACTION
                    
                    Goto Branch_End

               END
            
            Branch_Donothing:
                SET @NEW_BLOCK_ID = NULL
                SET @RESULT = 0
                SET @RESULTMSG = '该房源已被选，无法修改！'
                COMMIT TRANSACTION
                 Goto Branch_End
            
            Branch_DELOK:
                SET @NEW_BLOCK_ID = NULL
                SET @RESULT = 1
                SET @RESULTMSG = '删除成功！'
                COMMIT TRANSACTION
                Goto Branch_End
            
                
            Branch_End:
        END TRY
        BEGIN CATCH
            SET @NEW_BLOCK_ID = NULL
            SET @RESULT = 0
            SET @RESULTMSG = '出现错误！错误代码：' + @ERR_CODE
            ROLLBACK TRANSACTION
            
        END CATCH
        


END

上面的代码注意的是：在你goto   Branch_End之前，一定要先提交事务才去跳转