SQL server - stored procedure - ignore exception

Question

I have inside a stored procedure:

INSERT INTO SitesCategories(SiteID, CategoryID) VALUES(@SITEID, @TempCId); 

This insert can throw exceptions because I have this restriction in the table:

  ALTER TABLE dbo.SitesCategories ADD CONSTRAINT UniqueSiteCategPair UNIQUE (SiteId,CategoryID); 

I made this restriction, so I would not have to check when inserting uniques from pairs (@SITEID, @TempCId). But I do not want the SQl exception to be thrown when this is executed in the stored procedure. How can I catch an exception inside a stored procedure and continue operations inside the procedure?

Answer 1

If you create your UNIQUE CONSTRAINT using the IGNORE_DUP_KEY = ON parameter, then duplicates will be ignored without any problems, for example. they will not be inserted into your table, but an exception will not be thrown:

  ALTER TABLE dbo.SitesCategories ADD CONSTRAINT UniqueSiteCategPair UNIQUE (SiteId,CategoryID) WITH (IGNORE_DUP_KEY = ON); 

Answer 2

  BEGIN TRY INSERT INTO SitesCategories(SiteID, CategoryID) VALUES(@SITEID, @TempCId); END TRY BEGIN CATCH END CATCH;