i have a sproc that inserts data into 3 tables. let's say the sproc barfs
on the 2nd insert. i'm confused as to why i'm left with data in the first
table, as a result of the 1st insert, even though i've told it to rollback.
with what occurs when i run this and i get an error in the results window of
query analyzer, instead of the correct message "insert failed" that my sproc
is supposed to return. if for some reason the 2nd insert isn't able to run,
i'll get a message in the results window, instead of my code handling it.
my code - in simplified form - is following. any insight would be
ALTER PROCEDURE usp_insert_user
set nocount on
first insert the new user
-- get the id of the user just inserted
then insert the site(s) the user has access to
then insert the report(s) the user has access to
select 'insert successful' as result
Raiserror ('Insert Failed', 16, 1)