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|||
|||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..
CREATE PROCEDURE SaveUser
@.EmailAddress VARCHAR(256),
@.ListID INT
AS
DECLARE @.UserID INTSELECT @.UserID = ID FROM EmailUsers WHERE Email = @.EmailAddress
IF @.UserID IS NULL
BEGIN
INSERT INTO EmailUsers (Email) VALUES (@.EmailAddress)
SET @.UserID = @.@.IDENTITY
ENDINSERT INTO ListUsers (ListID, UserID) VALUES (@.ListID, @.UserID)
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
