From: paul_smith1@talk21.com
To: pgsql-sql@postgresql.org
Date: Thu, 20 Mar 2003 12:46:37 GMT+00:00
Subject: SQL stored proc query (optimising)
Mime-Version: 1.0
Content-type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: 
 <20030320124717.KHHI27722.wmpmta01-app.mail-store.com@wmpmtavirtual>

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 na=
me 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 =3D @strDrivername
  IF NOT  EXISTS(SELECT ID FROM tblDrivers WHERE txtDriversName =3D @strDri=
vername)=20
  BEGIN
     INSERT INTO tblDrivers (txtDriversName) VALUES (@strDrivername)
     SELECT ID FROM tblDrivers WHERE txtDriversName =3D @strDrivername=20=
=20
  END
IF @@ERROR <> 0
  BEGIN
       ROLLBACK TRAN
       RETURN @@ERROR
     END

COMMIT TRAN=20
RETURN
GO

This works fine, but I have to access the table twice, one to get the ID an=
d once to check if they are in the table. Is there a better way I can do th=
is 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.talk=
21.com