T-SQL存储过程中try和catch以及错误处理的用法例子

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ImportPlatformParam] 
	-- Add the parameters for the stored procedure here
	@mOrderID_Source nvarchar(64),
	@mProjVersion_Source nvarchar(2),
	@mOrderID_Target nvarchar(64),
	@mProjVersion_Target nvarchar(2)
AS
DECLARE @SQLStr nvarchar(4000), @count int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRAN
			--删除目标平台的平台参数
			DELETE FROM	UDSGKS_ProjPlatformParamMap
			WHERE		OrderID = @mOrderID_Target AND
						ProjVersion = @mProjVersion_Target

			--删除目标平台的平台非标点
			DELETE FROM	UDSGKS_ProjNSList
			WHERE		OrderID = @mOrderID_Target AND
						ProjVersion = @mProjVersion_Target

			--导入源平台的平台参数到目标平台
			INSERT INTO	UDSGKS_ProjPlatformParamMap
						(	OrderID, ProjVersion, 
							PlatformParamName, PlatformParamValue,
							PlatformParamDesc, PlatformParamType, FileName)
			SELECT	@mOrderID_Target AS OrderID,
					@mProjVersion_Target AS ProjVersion,
					PlatformParamName, PlatformParamValue,
					PlatformParamDesc, PlatformParamType, FileName
			FROM	UDSGKS_ProjPlatformParamMap
			WHERE	OrderID = @mOrderID_Source AND
					ProjVersion = @mProjVersion_Source
	
			--导入源平台的非标难度时间到目标平台
			DELETE FROM	UDSGKS_ProjDifficulty 
			WHERE		OrderID = @mOrderID_Target AND
						ProjVersion = @mProjVersion_Target

			INSERT INTO	UDSGKS_ProjDifficulty
			SELECT		@mOrderID_Target AS OrderID, @mProjVersion_Target AS ProjVersion,
						PDPeriod, PRPeriod, MDPeriod, MRPeriod, LDPeriod, LRPeriod
			FROM		UDSGKS_ProjDifficulty
			WHERE		OrderID = @mOrderID_Source AND
						ProjVersion = @mProjVersion_Source

			--导入源平台的平台非标点到目标平台
			INSERT INTO	UDSGKS_ProjNSList
						(	OrderID, ProjVersion, 
							NSID, NSFuncOption, NSRange, SchemaDesc,
							ModelID, Manner, MaterialDesc, PartID, PartName,
							PartSchema, MaterialID, DrawID, Remark, Flag, Hours)
			SELECT	@mOrderID_Target AS OrderID,
					@mProjVersion_Target AS ProjVersion,
					NSID, NSFuncOption, NSRange, SchemaDesc, ModelID,
					Manner, MaterialDesc, PartID, PartName, PartSchema,
					MaterialID, DrawID, Remark, Flag, Hours
			FROM	UDSGKS_ProjNSList
			WHERE	OrderID = @mOrderID_Source AND
					ProjVersion = @mProjVersion_Source

			--导入源平台的附言和备注到目标平台
			UPDATE	UDSGKS_PlatformTask
			SET		Postscripts = T1.Postscripts,
					Remark = T1.Remark
					FROM	(	SELECT Postscripts,Remark
								FROM	UDSGKS_PlatformTask
								WHERE	OrderID = @mOrderID_Source AND
										ProjVersion = @mProjVersion_Source) AS T1	
			WHERE	OrderID = @mOrderID_Target AND
					ProjVersion = @mProjVersion_Target
			
		COMMIT TRAN	
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN
		DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT
        SELECT @ErrMsg = ERROR_MESSAGE(),
               @ErrSeverity = ERROR_SEVERITY()
		RAISERROR (@ErrMsg, @ErrSeverity, 1)
	END CATCH
END