Skip to main content
mmEllen
Participating Frequently
Apr 15, 2020 5:27 PM UTC
Question

question about store procedure to return @@indentity

Our application just is upgrating to ColdFusion 2018. We have a store procedure

CREATE PROC sp_cs_set_emp_qual

@pbn_SRI varchar(15),

@84805_ID decimal(18,0),

@availability_status varchar(15) = null,

@84805_lname varchar(50),

@84805_fname varchar(50),

@84805_init varchar(50),

@9301045_qual varchar(250),

@8668307_emp_flag int,

@added_emp_ID decimal(18,0) OUTPUT

 

AS

 

  BEGIN

        INSERT INTO employee

 

                                        (   pbn_SRI,

                                            availability_status,

                                                emp_lname,

                                                emp_fname,

                                                emp_init,

                                                academic_qual,

                                                duplicate_ind

                                        )

        VALUES

                       (    @pbn_sri,

                                        @availability_status,

                                                @84805_lname,

                                                @84805_fname,

                                                @84805_init,

                                                @9301045_qual,

                                                @8668307_emp_flag

                                        )

 

     

SELECT @added_emp_ID = @@IDENTITY

END

With the ColdFusion 2018 developing edition, after runing this store procedure, the record has been insert into the table, but the @added_emp_ID return empty. It was working well with the ColdFuion 9. Anyone has an idea about this?

 

    This topic is closed to new replies. Start a new post to keep the conversation going.

    23 replies

    WolfShade
    WolfShade
    Legend
    Apr 16, 2020 4:49 PM UTC

    Hello, mmEllen,

     

    This should still work.  I'm not sure why it isn't.  But going from CF9 => CF2018 is a _HUGE_ jump, and you're bound to have issues.

     

    If you can't fix this, then I would suggest having CF create the UUID (I assume you're using UUID/GUID) and pass that to the Stored Procedure.  That way, you'll have it before it gets inserted into the database, and can reference that for further processing.

     

    HTH,

     

    ^ _ ^

    Translate
    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    Apr 17, 2020 4:57 PM UTC

    Or, taking a different tack: how are you calling the SP? with cfstoredproc? And with a cf procresult? And that's unchanged, I assume?

     

    Note that CF DSN's now offer a way to control whether a cfquery or cfstoredproc call should cause CF to have the identity col value returned automatically, and you can even control that for a cfquery with its new disableAutoGenKeys attribute. I wonder if that setting (in the DSN) may be impacting the value you are (not) getting back from the SP call.

    Translate
    /Charlie (troubleshooter, carehart. org)
      mmEllenAuthor
      Participating Frequently
      Apr 17, 2020 5:15 PM UTC

      This is the way the store procedure is called. How to setup CF DNS to make identity col value returned automatically?

       

      <CFSTOREDPROC PROCEDURE="sp_cs_set_emp_qual" DATASOURCE="#APPL_DATA#">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="15" TYPE="In" VALUE="#trim(request.session.firmInfo.pbn_SRI)#" DBVARNAME="@pbn_sri" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_DECIMAL" TYPE="In" VALUE="#trim(request.session.emp_ID)#" DBVARNAME="@emp_ID" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="15" TYPE="In" VALUE="#trim(request.session.emp_qual.availability_status)#" DBVARNAME="@availability_status" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_lname)#" DBVARNAME="@emp_lname" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_fname)#" DBVARNAME="@emp_fname" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="1" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_init)#" DBVARNAME="@emp_init" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="255" TYPE="In" VALUE="#trim(request.session.emp_qual.academic_qual)#" DBVARNAME="@academic_qual" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_INTEGER" TYPE="In" VALUE="#trim(request.session.emp_qual.dup_emp_flag)#" DBVARNAME="@dup_emp_flag" NULL="No">

      <CFPROCPARAM CFSQLTYPE="CF_SQL_DECIMAL" TYPE="Out" DBVARNAME="@added_emp_ID" VARIABLE="request.session.added_employee_ID">

      </CFSTOREDPROC>

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