Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

SQL stored procedure error with constraint

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?
0
RustyZ32
Asked:
RustyZ32
  • 2
  • 2
1 Solution
 
sqlxlCommented:
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
0
 
RustyZ32Author 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.
0
 
sarabhaiCommented:
other option like

BEGIN TRY
    insert statement;
END TRY
BEGIN CATCH
     Errorhandle code      ;
END CATCH;
0
 
sarabhaiCommented:
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
0
 
RustyZ32Author Commented:
I did the try/catch in the C# code of the app, this seems to work well. <br /><br />thanks for your help
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now