• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Catch error in insert select

I want to perform select insert. If error on inserting or update, it should roll back and return "Error message" if not it should return "Successful". Is this correct? I am not sure how to do this.


begin transaction

insert into table_1
(date, name, username)
select date, name, username
from table_2

update table ...etc

if @@error <> 0
begin
    if @@trancount > 0
        rollback
    return
end

commit
0
VBdotnet2005
Asked:
VBdotnet2005
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
BEGIN TRY
   BEGIN TRAN tr

   -- do your stuff here

   -- If code execution makes it here, good to go.
   COMMIT TRAN tr
END TRY

BEGIN CATCH
   -- If code execution makes it here then an error occured.   Rollback everything that happened up to the BEGIN TRANS.
   ROLLBACK TRAN tr
END CATCH
0
 
VBdotnet2005Author Commented:
Hi Jim,

Can it be returned with a message? If errors, it return "my error message", if no error, it should return " my message".
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sure, right before the ROLLBACK, something like...

SELECT 'An error occured' + ERROR_PROCEDURE() as message, 
   ERROR_NUMBER() as number, 
   ERROR_MESSAGE() as message

Open in new window

0
 
VBdotnet2005Author Commented:
Thank you very much Jim
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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