Thread: SQL stored proc query (optimising)

I'm currently developing a database in which I have a list of drivers. I've written a stored procedure for MS SQL
Serverwhich looks up the drivers name and returns their ID if they are already in the table, otherwise it adds them and
thenreturns 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
FROMtblDrivers 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
therea 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

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