pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SQL stored proc query (optimising)
2+ messages / 2 participants
[nested] [flat]

* SQL stored proc query (optimising)
@ 1970-01-01 00:00 paul_smith1@talk21.com
  2003-03-20 15:08 ` Re: SQL stored proc query (optimising) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: paul_smith1@talk21.com @ 1970-01-01 00:00 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL stored proc query (optimising)
  1970-01-01 00:00 SQL stored proc query (optimising) paul_smith1@talk21.com
@ 2003-03-20 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-03-20 15:08 UTC (permalink / raw)
  To: paul_smith1@talk21.com; +Cc: pgsql-sql

paul_smith1@talk21.com writes:
> I'm currently developing a database in which I have a list of
> drivers. I've written a stored procedure for MS SQL Server

This list is not for discussing MS SQL.  Please direct your question
to a more approriate forum.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-20 15:08 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 SQL stored proc query (optimising) paul_smith1@talk21.com
2003-03-20 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>

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