Maybe! I have looked at the Execution Plan for either! I literally just jotted down from memory something I have used in the past!
I do think, however that an IF NOT EXISTS can have a greater hit on resources. YMMV :-p -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 24 July 2006 16:37 To: SQL Subject: Re: stored procedure branching best practices Would that be more efficient than this: CREATE PROCEDURE GetAgentID @Agent varchar(300) IF NOT EXISTS(SELECT AgentID FROM Agents where [EMAIL PROTECTED]) BEGIN Set NOCOUNT ON Insert into Agents(Agent) Values(@Agent) Select MAXID = @@Identity SET NOCOUNT OFF end ELSE SELECT Agentid as Maxid FROM Agents where [EMAIL PROTECTED] GO I assume so as your doing a single select to both check and get the pre-existing value where the code above is doing 2 selects. >CREATE PROCEDURE up_foo > >@yourvalue VARCHAR(100) > >AS > >DECLARE @searchID INT >SET @searchID = 0 > > >SELECT @searchID = [PK OF TABLE] > FROM [TABLE] > WHERE COLUMN = @yourvalue > > >IF @ searchID = 0 >BEGIN > > DO YOUR INSERT > > SELECT @@identity (or other ident type) >END > > >In your CF, a simple cfstoredproc call with a resultset will give you the >necessary new id or the existing key. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2523 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
