Would you mind publishing your tips & tricks somewhere or forwarding them?
Duane
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 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
