Skip to main content
A
alecken
Known Participant
January 21, 2011
Question

CFStoredproc syntax help, please?

  • January 21, 2011
  • 1 reply
  • 1997 views

I need to use cfstoredproc tags to call a str. proc. written in Sybase. This str. proc. requires 4 parameters to be passed in and will returns 4 variables back

So I wrote this (see the cfstoresproc script below )

I need to use the return results in my SQL insert statement. Please help!

The following is the declaration on the Sybase str. proc. site, just to show you the relation between the two:

CREATE PROC EmpStat

@FNAME   VARCHAR(10) ,
@LName   VARCHAR(15) ,
@MName   VARCHAR(15) ,
@Gender   VARCHAR(1) ,

@18615499    VARCHAR(40) out,

@10436154    VARCHAR(40) out,
@Data3    VARCHAR(40) out,
@Data4    VARCHAR(6) out

AS .......etc

<cfstoredproc procedure="EmpStat" datasource="#MyDSN#" returncode="Yes" debug="Yes">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@FName" value="#Trim(Emp.First_Name)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@LName" value="#Trim(Emp.Last_Name)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@MName" value="#Trim(Emp_MName)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@Gender" value="#Trim(Emp.Gender)#">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname="Data1">

  <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname="Data2">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname="Data3">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" dbvarname="Data4">

   <cfprocresult name="Data1"

   <cfprocresult name="Data2"

   <cfprocresult name="Data3"

   <cfprocresult name="Data4"

</cfstoredproc>

Or should I write it this way, what are the differences?

<cfstoredproc procedure="EmpStat" datasource="#MyDSN#" returncode="Yes" debug="Yes">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(Emp.First_Name)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(Emp.Last_Name)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(Emp_MName)#">

   <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"  value="#Trim(Emp.Gender)#">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable = "Data1">

  <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable = "Data2">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable = "Data3">

   <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable = "Data4">

   <cfprocresult name="Data1"

   <cfprocresult name="Data2"

   <cfprocresult name="Data3"

   <cfprocresult name="Data4"

</cfstoredproc>

This topic has been closed for replies.

1 reply

A
Adam Cameron.
Inspiring
January 21, 2011

The second option is closest: dbvarname is deprecated to the point of obsolescence, and the docs say it culd cause unexpected behaviour.  So you should never use them.

But you don't need the <cfprocresult> tags.  The output params from the proc will populate the OUT <cfprocparam> variables.

--

Adam

A
aleckenAuthor
Known Participant
January 21, 2011

If the second script is the suggested approach then I'm assuming the order of these "IN" variables should be the same as those listed in Sybase str. proc. ?

And if cfprocresult is not necessary, than I can just use Data1 to 4 in my insert, such as:

Insert into column1, column2, column3, column4 Values ('#Data1#', '#Data2#', '#Data3#', '#Data4#') ?

A
Adam Cameron.
Inspiring
January 21, 2011

Yup, on both counts.

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices