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
