Just wanted to share something that I just worked out how to do properly
(not everyone is a SQL guru by birth)
How often do you need to check if a record exists, and if not insert it?
This is so cool (but I might just be easily amused)
<cfquery name="addToWatchList" datasource="myDSN">
declare @userID int
SET @userID = <cfqueryparam cfsqltype="cf_sql_numeric"
value="#arguments.userID#">
declare @itemnumber int
SET @itemnumber = <cfqueryparam cfsqltype="cf_sql_numeric"
value="#arguments.itemnum#">
if (SELECT Count(*) FROM Watchlist WHERE
User_ID = @userID
AND itemnumber = @itemnumber) >= 1
Begin
SELECT 'Record already exists' as Results
End
else
Begin
INSERT INTO Watchlist (user_id, itemnumber) VALUES
(@userID, @itemnumber)
SELECT 'Record Added' as Results
End
</cfquery>
And it will return 'Record Added', or 'Record already exists' 1 SQL
statement from 2, I love it.
Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273020
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
