Pete,
This is well done. But I'd change your query to..
IF NOT Exists (SELECT C.id
FROM tblHalftimeCustomers C
WHERE C.email = @email)
BEGIN
INSERT INTO
tblHalftimeCustomers(fname,lname,email,dayphone,evephone,cellphone,
street1,street2,street3,city,state,zip)
VALUES
(@fname,@lname,@email,@dayphone,@evephone,@cellphone,
@street1,@street2,@street3,@city,@state,@zip);
SELECT @@identity AS custid;
END
ELSE
SELECT C.ID AS custid
FROM tblHalftimeCustomers C
WHERE C.email = @email
GO
"If Exists" is more readible than "count(*) . ... = 0"
I would also point out that this query which is the heard of your SP needed
be in an SP at all. It would run perfectly fine inside of a CFQUERY tag. I
like Stored procs - but I usually use them when I need more than one
connection or have conditions that are ALL database driven etc. The SP is
certainly a better abstraction and a better choice in a team environment
(for the most part).
Also consider using "SCOPE_IDENTITY()" instead of "@@identity" It probably
doesn't make much difference in this case, but if you had multiple insert
queries you would end up with a unreliable value in the identity field.
-Mark
-----Original Message-----
From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 04, 2006 10:28 AM
To: CF-Talk
Subject: CF and the Stored Procedure Newbie - First in an irregular
series of Eureka! moments.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228365
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
