Sql store procedure partten 存储过程样式

-- =============================================
-- Author:		Kevin Zhang
-- Create date: 2014-11-13
-- Description:	

-- Author:		Kevin Zhang
-- Update date: 2014-11-25
-- Description:	

-- =============================================

USE [TestDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Test]
GO

CREATE	PROCEDURE	[dbo].[Test]
(
	@Xml XML
)
AS
	BEGIN TRY
		BEGIN TRAN
			DECLARE @ErrorMessage    NVARCHAR(4000),
					@ErrorNumber     INT,
					@ErrorSeverity   INT,
					@ErrorState      INT,
					@ErrorLine       INT,
					@ErrorProcedure  NVARCHAR(200);
		
			-- TODO
			
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		-- Collect error infos and it will be raise error to its invoker
		SELECT  @ErrorNumber = ERROR_NUMBER(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorLine = ERROR_LINE(),
				@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();
	        
		-- Roll back any active or uncommittable transactions
		IF (XACT_STATE()) <> 0
		BEGIN
			ROLLBACK TRAN
		END
	    
		RAISERROR(@ErrorMessage, 
				  @ErrorSeverity, 
				  1,               
				  @ErrorNumber,
				  @ErrorSeverity,
				  @ErrorState,
				  @ErrorProcedure,
				  @ErrorLine
				 )
	END CATCH

GO