Skip to main content
N
ndgnc66
Participant
April 17, 2011
Answered

cfprocparam and stored procedure

  • April 17, 2011
  • 2 replies
  • 1193 views

Hi,

I tried to use the MSSQL Stored Procedure in combination with ColdFusion 9. I use the cfprocparam to pass the data to the stored proc. If I do it in the order how they are declared in stored procedure, it works fine. But how can I pass the data with named variables? It used to be the "dbvarname" function but I guess it is deprecated now. Do I have to use the same order in my ColdFusion component? That makes it quite difficult to handle. I hope there is a solution to pass the data with named variables.

Thanks for the answers.

    This topic has been closed for replies.
    Correct answer Adam Cameron.

    Does that mean that I can not use named variables for stored procedures?

    [shakes head in disbelief]

    I guess my previous posting was somehow unclear.

    There is no DBVARNAME attribute of CFPROCPARAM.  It was dropped from CF after CFMX6 (I think.  It might have been dropped after CF5).

    So: no, you cannot use it.  It does not exist.

    There is online docs for this stuff, btw:

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7d52.html

    --

    Adam

    2 replies

    N
    ndgnc66Author
    Participant
    April 17, 2011

    Well here is the code I tested:

    in my stored proc(MSSQL) I have defined the variables like this:

    ALTER PROCEDURE [dbo].[uspSimple]

    -- Add the parameters for the stored procedure here

    @arg_gb_id int,

    @arg_beschreibung nvarchar(90),

    @arg_extra nvarchar(60),

    @arg_modus nvarchar(300)

    AS

    BEGIN

    .

    .

    AND in my ColdFusion page like this:

    <cfstoredproc procedure="uspSimple" datasource="projektcontrolling_live">

         <cfprocparam cfsqltype="CF_SQL_INTEGER"   value="99" dbvarname="@arg_gb_id">

        <cfprocparam cfsqltype="CF_SQL_LONGVARCHAR"   value="a" dbvarname="@arg_beschreibung">

        <cfprocparam cfsqltype="CF_SQL_LONGVARCHAR"   value="c" dbvarname="@arg_modus">

        <cfprocparam cfsqltype="CF_SQL_LONGVARCHAR"   value="b" dbvarname="@arg_extra">

    I have changed the order for the variables @arg_modus and @arg_extra but gave the dbvarname explicitly. When I run the code, the stored proc saves the data sequentially. My dbvarname information is ignored.

    Does that mean that I can not use named variables for stored procedures?

    Thanks for the answers.

    A
    Adam Cameron.Correct answer
    Inspiring
    April 17, 2011

    Does that mean that I can not use named variables for stored procedures?

    [shakes head in disbelief]

    I guess my previous posting was somehow unclear.

    There is no DBVARNAME attribute of CFPROCPARAM.  It was dropped from CF after CFMX6 (I think.  It might have been dropped after CF5).

    So: no, you cannot use it.  It does not exist.

    There is online docs for this stuff, btw:

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7d52.html

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    April 17, 2011

    DBVARNAME is not deprecated (in which case it would still work), it's obsolete.  This stemmed from an earlier version of the DatDirect JDBC drivers that CF uses not reliably supporting them.  Whilst the drivers do now support named params again, CF has not reinstated DBVARNAME.

    If your proc only returns one record set, you can call it with CFQUERY.

    --

    Adam

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