From: paul_smith1@talk21.com
To: pgsql-sql@postgresql.org
Subject: SQL stored proc query (optimising)
Date: Thu, 20 Mar 2003 12:46:37 GMT+00:00
Message-ID: <20030320124717.KHHI27722.wmpmta01-app.mail-store.com@wmpmtavirtual> (raw)
I'm currently developing a database in which I have a list of drivers. I've written a stored procedure for MS SQL Server which looks up the drivers name and returns their ID if they are already in the table, otherwise it adds them and then returns their ID
-- Used to look up a driver name. If the name is not in the list it adds it to the lsit
CREATE PROCEDURE sp_findAndUpdateDrivers
(@strDrivername varchar(50))
WITH RECOMPILE AS
BEGIN TRAN
SELECT ID FROM tblDrivers WHERE txtDriversName = @strDrivername
IF NOT EXISTS(SELECT ID FROM tblDrivers WHERE txtDriversName = @strDrivername)
BEGIN
INSERT INTO tblDrivers (txtDriversName) VALUES (@strDrivername)
SELECT ID FROM tblDrivers WHERE txtDriversName = @strDrivername
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN @@ERROR
END
COMMIT TRAN
RETURN
GO
This works fine, but I have to access the table twice, one to get the ID and once to check if they are in the table. Is there a better way I can do this such that it will still return the ID but is more efficeient?
Thanks in advance
Paul
--------------------
talk21 your FREE portable and private address on the net at http://www.talk21.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul_smith1@talk21.com
Subject: Re: SQL stored proc query (optimising)
In-Reply-To: <20030320124717.KHHI27722.wmpmta01-app.mail-store.com@wmpmtavirtual>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox