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
