MS-SQL 2005의 TRY _ CATCH를 써보자

CREATE PROCEDURE NormalInsert(
	@Context nvarchar(100)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @StartTime    DATETIME
	SET @StartTime = GETDATE()

	DECLARE @LoopCount INT
	SET @LoopCount = 10000

	WHILE( @LoopCount > 0 )
	BEGIN
		BEGIN TRANSACTION
			INSERT SendBox(Context) values (@Context)
			IF( @@Error <> 0 or @@RowCount = 0)
			BEGIN
				ROLLBACK TRANSACTION
				RAISERROR('Error when processing INSERT statement or Task Table.',15,1)
				RETURN -100
			END

		COMMIT TRANSACTION

		SET @LoopCount = @LoopCount - 1
	END

	PRINT 'Elapsed Time : ' + convert( varchar(128), GETDATE() - @StartTime, 114 )

	RETURN
END
GO

Normal Insert 사용시
Elapsed Time : 00:00:02:653
Elapsed Time : 00:00:02:593
Elapsed Time : 00:00:02:560
Elapsed Time : 00:00:02:737
Elapsed Time : 00:00:02:593

CREATE PROCEDURE TryInsert(
	@Context nvarchar(100)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @StartTime    DATETIME
	SET @StartTime = GETDATE()

	DECLARE @LoopCount INT
	SET @LoopCount = 10000

	WHILE( @LoopCount > 0 )
	BEGIN
		BEGIN TRY
			BEGIN TRANSACTION
				INSERT SendBox(Context) values (@Context)
			COMMIT TRANSACTION
		END TRY

		BEGIN CATCH
			ROLLBACK TRANSACTION

			BEGIN
				RAISERROR('Error when processing INSERT statement or Task Table.',15,1)
				RETURN -100
			END
		END CATCH

		SET @LoopCount = @LoopCount - 1
	END

	PRINT 'Elapsed Time : ' + convert( varchar(128), GETDATE() - @StartTime, 114 )

	RETURN
END
GO

Try Insert 사용시
Elapsed Time : 00:00:02:860
Elapsed Time : 00:00:02:810
Elapsed Time : 00:00:03:063
Elapsed Time : 00:00:02:640
Elapsed Time : 00:00:02:767