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之前，一定要先提交事务才去跳转