Yeah, that ought to work. CF can't "see" your sql "if" logic, so it might as
well be one giant select statement for all it cares. On the SQL Server end I
would expect it to handle that just fine.
You could also write a stored procedure for this logic.
Mark
-----Original Message-----
From: Jim Davis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 28, 2004 1:35 AM
To: CF-Talk
Subject: Dumb Question About CFPARAM
I'm trying to optimize some queries and I've got a dumb question. The
current code is like this:
<!--- Check to see if the key exists ---> <cfquery ... >
SELECT SessionKey
FROM Table
WHERE SessionKey = <cfqueryparam ... >
</cfquery>
<!--- If it's a new entry, create it, if not update it ---> <cfif
local.CheckEntry.RecordCount EQ 0>
<!--- Add the Session information --->
<cfquery ... >
INSERT INTO Table ( SessionKey )
VALUES ( <cfqueryparam ... > )
</cfquery>
<cfelse>
<cfquery ... >
UPDATE Table
SET SessionKey = <cfqueryparam ... >
</cfquery>
</cfif>
What I'm going to do (this is SQL Server 2000) is replace that with an
"exists" test in a single query like so:
<!--- Check to see if the key exists ---> <cfquery ... >
IF EXISTS (
SELECT SessionKey
FROM Table
WHERE SessionKey = <cfqueryparam ... >
)
BEGIN
INSERT INTO Table ( SessionKey )
VALUES ( <cfqueryparam ... > )
END
ELSE
BEGIN
UPDATE Table
SET SessionKey = <cfqueryparam ... >
END
</cfquery>
My question is with <CFPARAM> - what's up in this case? Will it work fine
when all of the parameters all nested in IF statements like this? Will
there just be a big long list of BIND variables and will the statement
(INSERT or RUN) use the right ones?
Am I making any sense?
Jim Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188850
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
