October 22, 2003 at 2:46 am
I have the following stored procedure:
CREATE procedure user_add @password varchar(50), @dep_id int, @team_id
int, @login_name varchar(50), @title int, @forename varchar(50),
@surname varchar(50), @force_pwchange int, @start_date varchar(20),
@ext int, @role_id int, @resp_to int as
set dateformat dmy;
declare @userid int;
begin transaction
insert into users(dep_id, team_id, login_name, title, forename, surname,
password, force_pwchange, start_date, ext)
values(@dep_id, @team_id, @login_name, @title, @forename, @surname,
@password, @force_pwchange, cast(@start_date as datetime), @ext);
set @userid = (@@identity);
if (@@error <> 0) goto on_error
insert into roles(user_id, role_id, role_type)
values(@userid, @role_id, 1);
insert into user_hierarchy(user_id, resp_to)
values(@userid, @resp_to);
commit transaction
return(1)
on_error:
rollback transaction
return (0)
GO
the previous incarnation of this used @@identity in the two inserts after the insert into users as i (wrongly) assumed that @@identity wouldn't be updated if the inserted table didn't contain an identity column. This was highlighted when one of the developers ran the insert and it failed as the insert into user_hierarchy does not allow null values in user_id and, @@identity was null.
the problem is though the first insert completed and the error was generated when the middle tier retried the query.
i'm not entirely certain if this was the case as the developer wasn't sure that the user was added previously but i would appreciate it if you could give your opinions as to whether a) the stored procedure looks ok from a syntax point of view and b) is this the best way to handle errors and transaction rollback etc.
this method is used in most stored procedures that do multiple inserts in the database and i'm worried that if i've overlooked something then i could have a database full of garbage ... the present (in production) database hold some 200,000 appointments for employees in our company ...
many thanks in advance ...
Steve
October 22, 2003 at 9:09 am
am i 'billy nomates' ?
October 23, 2003 at 1:46 am
From a syntax point of view, this is correct. @@identity is a bit like @@error or @@rowcount. It gets populated with every query.
However, using @@identity, you could run into problems, since it returns the last inserted Identity ... period. Not taking sessions or tables or whatever into account.
Check the use of SCOPE_IDENTITY(). This will return the last value in the current scope (ie. the procedure or trigger or ...).
October 23, 2003 at 1:50 am
Doing a rollback like this is probably the best way.
However, you do not catch any errors in the 'second' level inserts.
October 23, 2003 at 6:18 am
many thanks,
i have added extra catches to get the errors in the other inserts ...
about the scope ...
wouldn't the transaction run only within the scope of the stored procedure ? or am i missing something ?
October 23, 2003 at 7:23 am
The problem with the @@IDENTITY has nothing to do with your transaction.
@@IDENTITY returns the identity value of the last insert in the database. It does not take different tables, transactions, sessions, connections, whatever into account.
Suppose you have two users : User 1 and User 2 (duh...), each doing some stuff. I try to add some textart here...
Time USER 1 USER 2
- ------ ------
| INSERT (1)
| @@IDENTITY = 1
| SCOPE_IDENTITY() = 1
| @@IDENTITY = 1
| SCOPE_IDENTITY() = NULL
|
V
_
| INSERT (1)
| INSERT (2)
| @@IDENTITY = 2
| SCOPE_IDENTITY() = 1
| @@IDENTITY = 2
| SCOPE_IDENTITY() = 2
V
So, in your procedure, you have a concurrency issue if you have multiple users connecting to the database.
With SCOPE_IDENTITY(), the scope of the user is taken into account.
Scope can be a lot of things, check BOL. In general, think of temporary tables being only available 'in the current scope'.
October 23, 2003 at 7:46 am
many thanks, i've changed it so that SCOPE_IDENTITY is used as the insert.
i (mistakenly) thought that @@identity was session, once again thanks for pointing me in the right direction ...
so much to learn, so little time 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply