Condition INSERT / ID Retrieval Problem

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)

Condition INSERT / ID Retrieval Problem

Author

Topic

Mr Fett
Starting Member

28 Posts

Posted - 2009-07-28 : 17:55:12

Hi all,

I'm new to stored procedures to pls forgive my ignorance. I'm sure that what I need is fairly common but I just can't work out the synatax. I'm trying to write a stored procedure that accepts passenger data, checks to see if that passenger already has a record, if not, inserts one and grabs the ID and if so retrieves the ID. Once this is done, the ID is then used to to do a simple insert. Probably easier just to show you what I have so far:

CREATE PROCEDURE dbo.createPassenger

@firstName NVARCHAR (120),
@lastName NVARCHAR (120),
@passportNo NVARCHAR (120),
@journeyID INT

AS

DECLARE @passengerID INT

SELECT passengerID FROM CHARTER_passengers WHERE LOWER(lastName) = LOWER(@lastName) LOWER(passportNo) = LOWER(@passportNo)

IF (@@rowcount = 0)

BEGIN

INSERT INTO CHARTER_passengers (firstName, lastName, passportNo)
VALUES (@firstName, @lastName, @passportNo, @passportExpiry)

SET @passengerID = SCOPE_IDENTITY()

END

**** THIS WORKS (I ASSUME) WHEN A NEW PASSENGER RECORD IS INSERTED AND @passengerID is retieved using SCOPE_INDENTITY() BUT IF NOT, HOW DO I GET THE PASSENGERID PULLED FROM THE FIRST QUERY?****

INSERT INTO CHARTER_passengerJourneyLink (journeyID, passengerID)
VALUES (@journeyID, @passengerID)

GO

Thanks in advance for any and all help!

Bob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 18:20:45

[code]CREATE PROCEDURE dbo.createPassenger
(
@firstName NVARCHAR (120),
@lastName NVARCHAR (120),
@passportNo NVARCHAR (120),
@journeyID INT
)
AS

SET NOCOUNT ON

DECLARE @passengerID INT

SELECT @passengerID = passengerID
FROM CHARTER_passengers
WHERE LOWER(lastName) = LOWER(@lastName)
AND LOWER(passportNo) = LOWER(@passportNo)

IF @passengerID IS NULL
BEGIN
INSERT CHARTER_passengers
(
firstName,
lastName,
passportNo,
passportExpiry
)
VALUES (
@firstName,
@lastName,
@passportNo,
@passportExpiry
)

SET @passengerID = SCOPE_IDENTITY()
END

INSERT CHARTER_passengerJourneyLink
(
journeyID,
passengerID
)
VALUES (
@journeyID,
@passengerID
)[/code]

N 56°04'39.26"
E 12°55'05.63"

Mr Fett
Starting Member

28 Posts

Posted - 2009-07-28 : 19:27:59

Wow - thanks Peso, that's perfect. I hope you don't mind but I have a couple of queries.

1) Whats the advantage of using ISNULL over @@rowcount
2) How would I return the @passengerID value to a recordset? Would I simply have to add

SELECT passengerID = @passengerID to the end?

Thanks again for your help - it is much appreciated.

Mr Fett

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 02:49:59

Either that, or make the @passengerID parameter as an OUTPUT parameter.
The latter is more convenient.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources