SQL Server Catch exception and continue

Question

Ive follow procedure:

alter procedure sp_insert_cities
(
    @txt_nome_cidade varchar(300),
    @txt_nome_estado varchar(150) = null,
    @txt_pais varchar(150) = null,
    @int_id_cidade int output
)
as
begin
            //Here an exception may occur
            insert into tb_cidades values(
            @txt_nome_cidade,
            @txt_nome_estado,
            @txt_pais)

            set @int_id_cidade = @@identity

            //Here i want to catch exception and continue executing the proc
            if(@@error <> 0)
            begin
            select @int_id_cidade = int_id_cidade 
            from tb_cidades 
            where 
            txt_nome_cidade = @txt_nome_cidade
            end

After if(@@error <> 0) line,i want to continue executing code even if there are any errors,but SQL throws an exception to my application and the code inside IF condition will not executed.

Any ideas?

You do not want to use @@identity, it is an unsafe command that will messwith your dataintegrity if you ever add triggers to the table which insert to other tables. Use OUTPUT or scope_identity() instead. — HLGEM
– HLGEM, Commented Dec 14, 2010 at 18:53
@@identity gives you the LAST identity value generated, which is not necessarily the identity value you want. if you have a trigger on tb_cidades that inserts into a history/log table and that has an identity column on it, then you get that identity value and not the identity value generated for the tb_cidades table. By using SCOPE_IDENTITY() you get the identity value generated within your current "scope", which would be on tb_cidades. — KM.
– KM., Commented Dec 14, 2010 at 19:39

Jeff Hornby · Accepted Answer · 2010-12-14 18:44:12Z

7

BEGIN TRY 
       insert into tb_cidades values( 
            @txt_nome_cidade, 
            @txt_nome_estado, 
            @txt_pais) 

            set @int_id_cidade = @@identity 
END TRY

BEGIN CATCH
           select @int_id_cidade = int_id_cidade   
            from tb_cidades   
            where   
            txt_nome_cidade = @txt_nome_cidade  
END CATCH

answered Dec 14, 2010 at 18:44

Jeff Hornby

13.8k4 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KM. Over a year ago

get rid of @@identity, use SCOPE_IDENTITY()

Jeff Hornby Over a year ago

@@identity v. SCOPE_IDENTITY() wasn't part of the question (although I do agree with you)

KM. Over a year ago

even thought the OP doesn't ask about @@identity, it is an obvious problem with the original code and easily fixed.

bobs · Accepted Answer · 2010-12-14 18:49:02Z

0

The following will try to run your command. You can put anything you want to run in the CATCH block, which will execute only when an error occurs. The remaining code after the CATCH will run with error or without error.

BEGIN TRY
   insert into tb_cidades values(
   @txt_nome_cidade,
   @txt_nome_estado,
   @txt_pais)

       set @int_id_cidade = @@identity
END TRY

BEGIN CATCH
  PRINT 'Error occurred'

END CATCH

if(@@error <> 0)
begin
select @int_id_cidade = int_id_cidade 
from tb_cidades 
where 
txt_nome_cidade = @txt_nome_cidade
end

answered Dec 14, 2010 at 18:49

bobs

22.3k12 gold badges71 silver badges81 bronze badges

Collectives™ on Stack Overflow

SQL Server Catch exception and continue

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related