How to suppress SQL Server generated error message

I run this code and get the expected result. The expected result is an error message hard coded. Additionally, I have error SQL Server is producing:
Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK_Products'. Cannot insert duplicate key in object 'Production.Products'. The duplicate key value is (1).


Question: How can I suppress this error? I want to just get my own error message.

I am getting:
Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK_Products'. Cannot insert duplicate key in object 'Production.Products'. The duplicate key value is (1).

The statement has been terminated.
Insert into Production.Products failed with error 2627

running:
use TSQL2012;
GO
DECLARE @errnum AS int;
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid,
unitprice, discontinued)
VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
SET @errnum = @@ERROR;
IF @errnum <> 0 -- Handle the error
BEGIN
PRINT 'Insert into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
END;
GO

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Add some color to what you mean by 'suppress'.  
Have the statement fail with no message?
Have the statement insert the rows it can, and ignor the rest?
Detect which rows would fail/generate the error, and gracefully message that?
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Sorry for the lack of clarity.

re:> Add some color to what you mean by 'suppress'.
I want to suppress the bold portion if possible because my own custom message is engough:
Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK_Products'. Cannot insert duplicate key in object 'Production.Products'. The duplicate key value is (1).

The statement has been terminated.
Insert into Production.Products failed with error 2627

Only the first item from your post is applicable I guess.

The bold portion is generated by SQL Server. I want to not to display if possible or advisable.
dameyCommented:
BEGIN TRY
     INSERT INTO Production.Products(productid, productname, supplierid, categoryid,
     unitprice, discontinued)
      VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
END TRY
BEGIN CATCH

     -- DO NOTHING
    Print(....)
END CATCH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks damey, it works.
use TSQL2012;
GO
DECLARE @errnum AS int;
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;

BEGIN TRY
   INSERT INTO Production.Products(productid, productname, supplierid, categoryid,
   unitprice, discontinued)
   VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
END TRY
BEGIN CATCH
   SET @errnum = @@ERROR;
   PRINT 'Insert into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
END CATCH

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.