Return ID after a conditional insert? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Return ID after a conditional insert?

Author

Topic

mosh1234
Starting Member

1 Post

Posted - 2008-08-16 : 15:01:15

Hi,

I have an SQL statement that I am basically doing an Insert if a similar record does not exist. I want to return the UserID of the new record if the Insert succeeds and if it does not I want to return the UserID of the record that already exists. If I do a (; SELECT @@IDENTITY AS RecordID) then I can get the last record inserted but if a similar record exists then I want to be able to get the UserID of that record if the insert does not perform due to the similar record. Any help would be appreciated. Here is my SQL statement.

INSERT INTO [User]([SiteID],[PrivilegeTypeID],[FName],[LName],[Address],[City],[State],[Zip],[Email],[Phone])
SELECT SiteID, PrivilegeTypeID, Fname, Lname, Address, City, State, Zip, Email, Phone
FROM
(SELECT '1' SiteID, '1' PrivilegeTypeID, 'g1' Fname, 'g1' LName, 'g' Address, 'g' City, 'SD' State, '23443' Zip, 'g2' Email, '234324' Phone ) T
WHERE not exists (select UserID from [User] where Fname = 'g1' and LName = 'g' and Email = 'g2')

chadmat
The Chadinator

1974 Posts

Posted - 2008-08-16 : 16:44:37

Why not populate a variable with the SELECT of UserId, then if it is NULL, do the insert and use SCOPE_IDENTITY() to get it. If not, well then you have the userid in a variable.

-Chad

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-16 : 16:46:27

DECLARE @UserID INT

SELECT @UserID = UserID FROM User WHERE Fname = 'g1' AND Lname = 'g' AND Email = 'g2'

IF @UserID IS NULL
BEGIN
INSERT INTO [User]([SiteID],[PrivilegeTypeID],[FName],[LName],[Address],[City],[State],[Zip],[Email],[Phone])
SELECT '1', '1', 'g1', 'g1', 'g', 'g', 'SD', '23443', 'g2', '234324'
SET @UserID = SCOPE_IDENTITY()
END

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-16 : 16:47:33

E 12°55'05.25"
N 56°04'39.16"

chadmat
The Chadinator

1974 Posts

Posted - 2008-08-16 : 17:25:22

quote:
Originally posted by Peso

DECLARE @UserID INT

SELECT @UserID = UserID FROM User WHERE Fname = 'g1' AND Lname = 'g' AND Email = 'g2'

IF @UserID IS NULL
BEGIN
INSERT INTO [User]([SiteID],[PrivilegeTypeID],[FName],[LName],[Address],[City],[State],[Zip],[Email],[Phone])
SELECT '1', '1', 'g1', 'g1', 'g', 'g', 'SD', '23443', 'g2', '234324'
SET @UserID = SCOPE_IDENTITY()
END

E 12°55'05.25"
N 56°04'39.16"

Yes, Like that, except I am not nice enough to write the code for you :). Besides, then I would have been the one getting sniped.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources