The execution plan shows yours to be better. I tried it your way first and got an error using the return syntax. That's why I went the EXISTS way. After seeing yours, I made some mods to my original and added a 'select @MaxID as AgentID' and it worked perfectly. Thanks
>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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2524 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
