We help IT Professionals succeed at work.

SQL stored procedure error with constraint

RustyZ32
RustyZ32 asked
on
I have a Stored proc with a single insert statement, the table has a unique constraint.

how can I make the stored procedure simply finish with 0 rows affected instead of crashing with the constraint error?
Comment
Watch Question

Commented:
Check for the constraint first.

Let's say you have a unique constraint on 'TransactionID'.

if exists(select 1 from sometable where TransactionID = @TransactionID)
begin
 return 0 --exit with doing an isnert.
end
else
begin
   --do the insert.
end

Author

Commented:
I tried something like that already, the problem is that the constraint is a combination of 3 columns all of which are int values. so simply adding the three together doesn't work because it just does the math. so I converted them first to varchar, this seems like a big performance hit.
Developer
BRONZE EXPERT
Commented:
other option like

BEGIN TRY
    insert statement;
END TRY
BEGIN CATCH
     Errorhandle code      ;
END CATCH;
sarabhaiDeveloper
BRONZE EXPERT

Commented:
BEGIN TRY
    insert statement;
END TRY
BEGIN CATCH
     Errorhandle code      ;//here you want 0 rows updated
END CATCH;

may at errorhandle code as
select 0 where 1=2

Author

Commented:
I did the try/catch in the C# code of the app, this seems to work well. <br /><br />thanks for your help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.