Would you care to share any of these? One of the main reasons I moved away
from using cfstoredproc is that I found it very hard to debug. I would
frequently just get an error that basically said "It didn't work", but not
much other information. In one particular situation, it was an access issue
(the user didn't have access to the SP). cfstoredproc didn't tell me this,
but cfquery did. I have also had problems with cfstoredproc in that the
order of the cfprocparam tags had to be identical to the order of the
parameters in the stored procedure (basically it was a huge pain in the
behind). I also find it much simpler to dynamically build my SP calls using
the cfquery syntax.
Could you share the reasons that you've decided to use cfstoredproc as much
as possible, and could you share any of these no doubt useful tips and
tricks?
Thanks,
Bob
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 4:41 PM
To: CF-Talk
Subject: RE: cfstored procedure- NEWbie
btw
We always use cfstoredproc to call or sps, except if we need to cache the
queries. and we have come up with a number of tricks, tips whatever for all
of the different scenarios.
-----Original Message-----
From: Greenberg, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 3:25 PM
To: CF-Talk
Subject: RE: cfstored procedure- NEWbie
Thank you very much for your help!! The changes to cfprocparam didn't work,
but the CFquery option did.
I may need to return more than result set at some point, so I'll need to
figure out why the 1st option isn't working, but for now I'll live with the
other option.
-----Original Message-----
From: Bob Silverberg [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 1:00 PM
To: CF-Talk
Subject: RE: cfstored procedure- NEWbie
I notice a few things (don't know if they are causing the problem, but I
noticed them):
1. I don't think you should be putting the @ sign on the names of your
variables in your <cfprocparam> tag. (Change dbvarname="@ZIP" to
dbvarname="ZIP".
2. In one <cfprocparam> tag you are surrounding the value of the dbvarname
attribute with quotes and in the other you aren't. This shouldn't cause a
problem, but it makes the code confusing.
3. You are declaring an output parameter but you never place a value in it
in the procedure.
If you're only returning one result set, and you aren't using output
parameters, I prefer to use the <cfquery> tag to call my stored procedures.
The syntax would be:
<CFQUERY datasource="LGDB" name="outzip">
Exec getSRzip @ZIP='#form.TERRITORY_ZIP#'
</CFQUERY>
You may want to try that instead. I find I get much more meaningful error
messages when I use <CFQUERY>.
Bob
-----Original Message-----
From: Greenberg, Lisa [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 3:11 PM
To: CF-Talk
Subject: cfstored procedure- NEWbie
I am trying to get a result set back from SQLSERVER using the following
stored procedure. I know I should get a recordcount > 0 ( I checked this in
Enter. Manager). However when I execute this stored procedure the rowcount
I get a value of '0'. What am I doing wrong?
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<CFSTOREDPROC PROCEDURE="getSRzip" datasource="LGDB" returncode="Yes"
debug="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@ZIP"
value="#form.TERRITORY_ZIP#" null="Yes">
<CFPROCPARAM TYPE="OUT" variable="territory_type" cfsqltype="CF_SQL_CHAR"
DBVARNAME=@territory_type>
<CFPROCRESULT name="outzip" resultset="1">
</CFSTOREDPROC>
<CFSETTING ENABLECFOUTPUTONLY="No">
<cfoutput>#outzip.RecordCount#</cfoutput>
<cfoutput query="outzip">#Territory_Type#<br>
</cfoutput>
CREATE PROCEDURE getSRZip (
@ZIP CHAR (5),
@TERRITORY_TYPE VARCHAR(15) OUTPUT)
AS
SELECT TERRITORY_TYPE,
ZIP,
EMAIL
FROM V_TERRITORY_ALIGNMENT
WHERE TERRITORY != " "
AND TERRITORY_ID !=114
AND ZIP =@ZIP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
