Make sure that all variables appear in the same order in your cfstoredproc
tags as they do in your SQL.
William J. Riley
Administrator
Atlantic Computer Systems & Services
Edge Web Hosting
Phone:(410) 363-4626
Fax:(410) 363-8526
[EMAIL PROTECTED]
http://www.edgewebhosting.net
-----Original Message-----
From: Derek Hamilton [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 17, 2000 4:46 PM
To: CF-Server
Subject: Cfstoredprocedure
I was just wondering if anyone out there has had much experience with the
cfstoredproc tag? I was using it in a custom tag and I noticed that it
would shift variable values. When I executed the stored procedure on the
server with regular SQL it ran fine so it definitely was a coldfusion thing.
I also changed the code in the custom tag to just a regular cfquery tag with
SQL running the stored procedure and it ran fine. The only problem is that
I would like to do error checking with the statuscode returned from the sp
and according to cf docs you have to use the cfstoredproc tag for this.
Any suggestions? The code in the custom tag and the calling code is below:
***** Calling Code *****
<CF_cf_lempinsert HANDLE="1" UNIQUE_ID="#unique_id#"
REPORT_NUM="#report_num#" CLIENT_ID="#client_id#" CLI_NAML="#cli_naml#"
CLI_NAMF="#cli_namf#" CLI_NAMT="#cli_namt#" CLI_NAMS="#cli_nams#"
CLI_ADR1="#cli_adr1#" CLI_ADR2="#cli_adr2#" CLI_CITY="#cli_city#"
CLI_ST="#cli_st#" CLI_ZIP4="#cli_zip4#" CLI_PHON="#cli_phon#"
EFF_DATE="#eff_date#" CON_PERIOD="#con_period#" AGENCYLIST="#agencylist#"
DESCRIP="#descrip#" USER_ID="#user_id#">
***** Custom Tag *****
<cfstoredproc procedure="sp_insert_lemp_register" datasource="ColdFusion"
returncode="Yes">
<cfprocparam type="in" dbvarname="handle" value="1"
cfsqltype="CF_SQL_INTEGER" null="No">
<cfprocparam type="in" dbvarname="unique_id"
value="#attributes.unique_id#" cfsqltype="CF_SQL_CHAR" null="No">
<cfprocparam type="in" dbvarname="report_num"
value="#attributes.report_num#" cfsqltype="CF_SQL_CHAR" null="No">
<cfprocparam type="in" dbvarname="client_id"
value="#attributes.client_id#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_naml"
value="#attributes.cli_naml#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_namf"
value="#attributes.cli_namf#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_namt"
value="#attributes.cli_namt#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_nams"
value="#attributes.cli_nams#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_adr1"
value="#attributes.cli_adr1#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_adr2"
value="#attributes.cli_adr2#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_city"
value="#attributes.cli_city#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_st" value="#attributes.cli_st#"
cfsqltype="CF_SQL_CHAR" null="No">
<cfprocparam type="in" dbvarname="cli_zip4"
value="#attributes.cli_zip4#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="cli_phon"
value="#attributes.cli_phon#" cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="eff_date" value="#effec_date#"
cfsqltype="CF_SQL_CHAR" null="No">
<cfprocparam type="in" dbvarname="con_period" value="#contract#"
cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="agencylist" value="#agencies#"
cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="descrip" value="#description#"
cfsqltype="CF_SQL_VARCHAR" null="No">
<cfprocparam type="in" dbvarname="user_id" value="#attributes.user_id#"
cfsqltype="CF_SQL_CHAR" null="No">
</cfstoredproc>
----------------------------------------------------------------------------
--
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
------------------------------------------------------------------------------
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
