Professional Web Applications Themes

Can't get return value from <cfstored Proc> - Coldfusion Database Access

I am using a select statement at the end of a record insert to retreive the identity value of the new record. This is in SQL Server 2000. The code is as follows: CREATE PROCEDURE dbo.InsertAccount dpRoleKey int AS Insert Into Account(DPRoleKey ) Values(dprolekey) Select accountKey = Scope_Identity() GO I tried to retreive the accountKey with the <cfprocresult> and no luck. I also tried to use the <cfprocparam> with the type set to "Out". Still no luck. Could someone point me in the right direction on how to get this value back with <cfstoredproc>? Thanks for the assist. Mark F...

  1. #1

    Default Can't get return value from <cfstored Proc>

    I am using a select statement at the end of a record insert to retreive the
    identity value of the new record. This is in SQL Server 2000. The code is as
    follows:

    CREATE PROCEDURE dbo.InsertAccount

    dpRoleKey int

    AS

    Insert Into Account(DPRoleKey )
    Values(dprolekey)


    Select accountKey = Scope_Identity()

    GO

    I tried to retreive the accountKey with the <cfprocresult> and no luck. I
    also tried to use the <cfprocparam> with the type set to "Out". Still no luck.
    Could someone point me in the right direction on how to get this value back
    with <cfstoredproc>? Thanks for the assist.

    Mark F


    Mark Forsberg Guest

  2. #2

    Default Re: Can't get return value from <cfstored Proc>

    I forgot to mention that I also declared the accountKey as an Int. Sorry.
    Mark Forsberg Guest

  3. #3

    Default Re: Can't get return value from <cfstored Proc>

    Perhaps try using the keyword of OUTPUT in your variable declaration.

    CREATE PROCEDURE dbo.InsertAccount

    dpRoleKey int,

    accountKey int = NULL OUTPUT

    AS

    Insert Into Account(DPRoleKey )
    Values(dprolekey)

    Select accountKey = Scope_Identity()


    Phil

    paross1 Guest

  4. #4

    Default Re: Can't get return value from <cfstored Proc>

    Mark,

    To retrieve the value, either use an output parameter with <cfprocparam>
    (type="OUT") as Phil has described or return the value in a resultset and use
    <cfprocresult> to caputure the value. Since you are only retrieving a single
    value, the output parameter method is probably more efficient.

    --- not tested
    CREATE PROCEDURE dbo.InsertAccount dpRoleKey int
    AS
    BEGIN

    Insert Into Account(DPRoleKey )
    Values(dprolekey)

    Select Scope_Identity() AS accountKey

    END
    GO

    <cfstoredproc ...>
    <cfprocparam type="in" dbVarName="dpRoleKey" ....>
    <cfprocresult name="yourResultQuery">
    </cfstoredproc>

    <cfoutput>#yourResultQuery.accountKey#</cfoutput>


    mxstu Guest

Similar Threads

  1. CFSTORED procesure error
    By BigNicko7 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: April 19th, 09:58 PM
  2. stored proc RETURN value in ASP
    By ben h in forum ASP Database
    Replies: 2
    Last Post: June 17th, 11:02 AM
  3. Replies: 0
    Last Post: September 9th, 12:08 AM
  4. How to 'return' from a Proc?
    By Kero van Gelder in forum Ruby
    Replies: 1
    Last Post: July 7th, 05:08 AM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139