Programming & Software Support
- Enabling SMS Feature for China Unicom VN007+ 5G Modem
- Generating Running Balance or Statement of Acount in SQL
- Model Binded Custom HTML Helper for C# MVC
- Easyworship 2009 fix for Windows 10 Nov. 2015 Update
- Simple way of encrypting and decrypting sensitive data using phrase
- HTML Color Chart
- Getting The Last Column Of The Last Row Of A Table with jQuery
- Microsoft SQL Server Transactional and Merge Replication step by step
- Check for duplicates before inserting a record
- Creating Stored Procedure with Output Variable
Categories
we should use real table but we will use temporary table at this time for the sake of testing.
DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')
declare our input variable
DECLARE @inputID varchar(max) = 'c'
DECLARE @inputDescription varchar(max) = 'cat'
Check the record if exists. If the record exists, raise an error
IF EXISTS( SELECT 1
FROM @items
WHERE ID = @inputID
) BEGIN
RAISERROR ('Record Already Exists', 11, 1)
END
let's insert and check our new record
INSERT INTO @items
VALUES (@inputID, @inputDescription)
SELECT *
FROM @items
let's wrap it up with a TRY CATCH
DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')
BEGIN TRY
DECLARE @inputID varchar(max) = 'c'
DECLARE @inputDescription varchar(max) = 'cat'
IF EXISTS( SELECT 1
FROM @items
WHERE ID = @inputID
) BEGIN
RAISERROR ('Record Already Exists', 11, 1)
END
INSERT INTO @items
VALUES (@inputID, @inputDescription)
SELECT *
FROM @items
END TRY
BEGIN CATCH
END CATCH
Let's make it transactional. Rollack all changes on error
BEGIN TRY
BEGIN TRANSACTION
DECLARE @inputID varchar(max) = 'c'
DECLARE @inputDescription varchar(max) = 'cat'
... ... ...
SELECT *
FROM @items
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Let's re-raise the error
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @errorMessage NVARCHAR(MAX), @errorSeverity INT, @errorState INT
SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE()
RAISERROR(@errorMessage, @errorSeverity, @errorState)
END CATCH
And it's done
DECLARE @items table (ID VARCHAR(2), [DESCRIPTION] VARCHAR(50))
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('a','apple')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('b','bag')
INSERT INTO @items (ID, [DESCRIPTION]) VALUES ('c','cat')
BEGIN TRY
BEGIN TRANSACTION
DECLARE @inputID varchar(max) = 'c'
DECLARE @inputDescription varchar(max) = 'cat'
IF EXISTS( SELECT 1
FROM @items
WHERE ID = @inputID
) BEGIN
RAISERROR ('Record Already Exists', 11, 1)
END
INSERT INTO @items
VALUES (@inputID, @inputDescription)
SELECT *
FROM @items
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @errorMessage NVARCHAR(MAX), @errorSeverity INT, @errorState INT
SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE()
RAISERROR(@errorMessage, @errorSeverity, @errorState)
END CATCH
Back To Programming & Software Support