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:2522
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
