Double Table Insert

Tables :

EmailUsers
ID int - PK
Email nvarchar(256)

ListsUsers
ListID int - FK to List Table - Combo PK
UserID int - FK to EmailUsers Table - Combo PK

When a person adds a user I need to:
A. insert them as a new entry into EmailUsers - no problem
B. insert their EmailUsers.ID from step A and ListID (passed in parameter) into ListsUsers - not so easy
C. if they're already in EmailUsers don't insert them but pass their existing EmailUsers.ID to part B

Any thoughts or examples I can follow? Maybe it's easier to do two seperate queries and control the if exists logic in asp.net?I would check out the starter kits--such as the TimeTracker. They illustrate the following:

if select Count(*) from emailusers where .. = 0
Begin
Begin Transaction

insert into Email users

select @.id = @.@.identity

if no error
continue

insert into ListUsers
(x, @.id)

Commit Trans
end
else
select @.id = id from emailusers

insert into ListUsers
(x, @.id)

Hope this points you in the right direction|||

CREATE PROCEDURE SaveUser
 @.EmailAddress VARCHAR(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 SELECT @.UserID = ID FROM EmailUsers WHERE Email = @.EmailAddress
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO EmailUsers (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 END
 INSERT INTO ListUsers (ListID, UserID) VALUES (@.ListID, @.UserID)

|||I'd say use SCOPE_IDENTITY() rather than @.@.IDENTITY.. if there was another insert at the same time @.@.IDENTITY will return the id of that insert. SCOPE_IDENTITY() will work within the scope of the insert..

hth|||ndinakar: Thanks for that little tidbit, I didn't realize there was a potential for scope issues with @.@.IDENTITY.|||it should be ok to use @.@.IDENTITY as there could be 1/1000 ( or more or less...just a guesstimate) chance of your insert coinciding with another one but that could very well runi your day...so...check out BOL for more info on them...

hth|||I was in Vegas for the past week. Will give the suggestions here a try on Monday morning.

Thanks!|||Works great but I'm slightly confused on my return value

CREATE PROCEDURE [dbo].[Add_List_Users]
 @.EmailAddress nvarchar(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 DECLARE @.returnCode INT
 SELECT @.UserID = ID FROM Email_Users WHERE Email = @.EmailAddress
 SET @.returnCode = 0
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO Email_Users (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 SET @.returnCode = @.returnCode + 1
 END
if not exists (select * from Lists_Users where ListID = @.ListID and UserID = @.UserID)
 INSERT INTO Lists_Users (ListID, UserID) VALUES (@.ListID, @.UserID)
 SET @.returnCode = @.returnCode + 2
return @.returnCode
GO

Scenarios:
User Added to Users Table and List Table - returnCode 3
User exists in Users Table and Added to List Table - return code 2
User exists in both tables - return code 0

Actual Return Values:
2
1
-1

That's fine, I just check for 2, 1, -1 instead of 3, 2, 0 but I'd like to know why the values returned aren't logically what they should be?

SET @.returnCode = 0
SET @.returnCode = @.returnCode + 1
SET @.returnCode = @.returnCode + 2
shouldn't that = 3??

It's as if @.returnCode starts off at -1 since all the return values are 1 less than I expect them to be.|||The SCOPE_IDENTITY really comes into play if you've got hidden inserts. For example, another insert triggered from your original insert. That's where simply returning @.@.IDENTIY becomes totally wrong. Like the poster said, use SCOPE_IDENTITY and you can sleep at night!|||plus..you might want to look at OUTPUT Parameters to return the id ..check out BOL for more info on OUTPUT parameters..

hth