Transaction Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Transaction Question

Author

Topic

mayan12
Starting Member

10 Posts

Posted - 2002-10-10 : 11:49:41

I got 2 tables.
FOr example:
members (Uid,Usrnm,UsrPass,Datecreated)

Members_data (UDid,Uid,Fname,lname,Address,email)

I need to create a SP that will enter data to both Members and Member_data.

What I have is
Insert into Members (Usrnm,UsrPass,Datecreated) Values ('Test','test','1/1/2002')

Insert into Members_data (Uid,Fname,lname,Address,email) Values (1'Test','test','Somewhere USA','Myemail@mydomain.com')

hers my problem

1. How do I get the ID of the Just inserted Record In Members (I know About the @@select I just dont know how to use it in SP's)

2. How do I make sure (Using transactions) That data went into the Members and members_data.

Example. Members Data goes in fine BUT members_data Insert fails. I have now a Useless record. I know about Begin Trans Commit and Rollback But Again I dont know the proper way of using them.

Any help would be appreciated

Edited by - mayan12 on 10/10/2002 11:50:58

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-10 : 12:14:58

declare @error int ,
@id int

begin tran
Insert into Members (Usrnm,UsrPass,Datecreated) Values ('Test','test','1/1/2002')
select @error = @@error
if @error <> 0
begin
raiserror('failed insert Members error = %d',16,-1,@error)
rollback tran
return
end
Insert into Members_data (Uid,Fname,lname,Address,email) Values (@id'Test','test','Somewhere USA','Myemail@mydomain.com')
if @error <> 0
begin
raiserror('failed insert Members_data error = %d',16,-1,@error)
rollback tran
return
end
commit tran
return

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lozitskiy
Starting Member

28 Posts

Posted - 2002-10-10 : 15:57:35

1. How do I get the ID of the Just inserted Record In Members (I know About the @@select I just dont know how to use it in SP's)

BOL:

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

@@ROWCOUNT
Returns the number of rows affected by the last statement.

-------------
MCP MSSQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources