[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
參考資料:自己