I am not a sql guru but check out
http://www.4guysfromrolla.com/webtech/010600-1.shtml
There is a lot of asp examples, but the sql stuff is great. Below is a code
example I grabbed from their site.
The if exists() tests to see if a record has been returned. If it has, The
sp returns a 1, if not it returns 0. The else could be a query or variable
declaration or whatever...
CREATE PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From Users
Where UserName = @UserName
And
Password = @Password)
return(1)
else
return(0)
Go
For anyone interested (I know your out there... <g>), the above is
equivalent to checking a login and password in your cfquery then checking if
the recordcount is greater than 0 and logging the user in if so. With this
code:
<cfset username = "test">
<cfset password = "test">
<cfquery datasource="northwind" name="checkuser">
SELECT Username, Password
FROM Users
WHERE Username = '#username#'
AND Password = '#password#'
</cfquery>
<cfif checkuser.recordCount GT 0>
User Exists
<cfelse>
New User
</cfif>
So how would one call the SP and why? How first...
First I added a table called user into the stock NorthWind database that
comes with SQL Server with two columns (username,password) and added one
record with the username as "test" and the password as "test". Then I added
the SP into sql server with a quick cut & paste. Finally below is the code
that does exactly the same thing as the above CF code.
<cfset username = "test">
<cfset password = "test">
<cfstoredproc procedure="sp_IsValidLogon" datasource="northwind"
returncode="Yes">
<CFPROCPARAM TYPE="IN"
VARIABLE="var"
DBVARNAME="username"
VALUE="#username#"
CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM TYPE="IN"
VARIABLE="var"
DBVARNAME="password"
VALUE="#password#"
CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCRESULT NAME="userExists">
</cfstoredproc>
<cfif cfstoredproc.statuscode EQ 1>
User Exists
<cfelse>
New User
</cfif>
Why would one do it with an SP? No code hunting if you change a db field
later on, just change the stored procedure and any cf page using it will go
on without one change. Keeping your queries in one place is nice too. Who
cares though, it's speed that matter right?
How about performace? Well this one surprised me. The <cfquery> is quite a
bit faster with one record in the db! In very unscientific tests, I looped
each bit of code 1000 times and the cfquery came in consistently around 1500
ms and the cfstoredproc came in at around 2000 ms. Ok, I said, there must be
a reason. So I decided to add more records. I added 88 more records, which
consisted of me alternating between banging on the keyboard and hitting tab
176 times into the table to approximate average internet users ;-) Quite
fun...
Any way, with 89 records the cfstoredproc barely budged, maybe up a few ms
on average. The cfquery however now averages 1750ms!
Well more banging ensued. Now I have 463 users.
cfstored proc is hanging is there not budging a ms, it's still at 2000 or
so, but the cfquery page is now at 2700ms. Aha! Speed...mmmm
Well, I still wasn't satisfied. So I deleted the user "test" record so that
neither query would return any results.
Guess what? The cfquery stuck to it's guns going up a little to 2900ms this
time, but cfstoredproc skyrocketed to an average 3400ms. I then inserted the
test user back into the table as the last record. Nothing changed. Inserting
the test user back to the first record and the speed record goes back to
cfstoredproc.
I suspect the reason behind this is the way I am determining if the record
exists. If the sp only returned a recordset it would probably be faster in
both cases, but since this post started out with demonstrating an if in SQL
(2.5 Mountain Dews ago), I'm not going to verify it now. Perhaps someone in
the know could enlighten me?
Generally though, the more complex a query and the more records it returns,
the larger the performance gain with stored procedures...
As pud would say...
Rock on
jon
----- Original Message -----
From: "Robert Everland III" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, January 30, 2001 6:43 PM
Subject: OT: SQL Stored Procedure Syntax help
> Ok basically what I am doing is passing a variable to a stored procedure.
> This variable is a number, based on the number it updates a record. What
> field is updated depends on what variable is passed. How would I go about
> doing this. I have filled in the variable for testing purposes.
>
> declare @return int
> declare @entry int
> exec @return = addaverage
> @entry = 1
>
> Update Users
> Set [@entry] = @return
> where id = 1
>
> should do this
> Update Users
> Set [1] = @return
> where id = 1
> but of course it doesn't do this. Is there a way to do ifs in there. I am
> new at this whole stored procedure thing, but I am expecting a huge
traffic
> site and want to take away as much as possible with processing on CF.
>
>
> Bob Everland
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
