[SQL]預存程序加上try catch機制

  • 779
  • 0

[SQL]預存程序加上try catch機制

簡易範本方便自己以後參考:

alter PROCEDURE [dbo].[s_Insert_YourTable]
   @EventCode bigint,
	@OutString varchar(200) out
AS
BEGIN
	--回傳結果
	SET @OutString = 'success';

	
	BEGIN TRY	
		--先檢查是否存在
		if not exists(select * from dbo.YourTable where EventCode = @EventCode )
		begin
			--不存在的話就直接insert
			insert into dbo.YourTable
			(EventCode, Status, CreateDateTime)
			values
			(@EventCode, 'queue', GETDATE())
		END
		
	END TRY
	BEGIN CATCH
		SET @OutString = N'exec [dbo].[s_Insert_YourTable]發生錯誤:' + CONVERT(VARCHAR(1000),ERROR_MESSAGE())
	END CATCH

END



參考資料:自己