Professional Web Applications Themes

Return Value from Stored Procedure - Microsoft SQL / MS SQL Server

I am having problems with a stored procedure not returning the identity to the ASP Page that called the stored procedure. Any ideas? The stored procedure is inserting the records, but I just cannot seem to get the identity. Here is the ASP and Stored Procedure. ASP: Set conn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") Set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = "p_FINAPRO_" & request.form("drpformlist") & "Form" cmd.CommandType = adCMDStoredProc cmd.Parameters.Append (cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)) cmd.Parameters.Append (cmd.CreateParameter("AccountNo", adInteger, adParamInput,, nAccountNumber)) '//SET THE REDCORDSET OBJECT Set rs = cmd.Execute response.Write("retval: " & cmd.Parameters("RetVal")) STORED PROCEDURE: CREATE PROCEDURE [dbo].[p_FINAPRO_100Form] AccountNo int ...

  1. #1

    Default Return Value from Stored Procedure

    I am having problems with a stored procedure not returning the identity to
    the ASP Page that called the stored procedure. Any ideas? The stored
    procedure is inserting the records, but I just cannot seem to get the
    identity.
    Here is the ASP and Stored Procedure.

    ASP:
    Set conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "p_FINAPRO_" & request.form("drpformlist") & "Form"
    cmd.CommandType = adCMDStoredProc
    cmd.Parameters.Append (cmd.CreateParameter("RetVal", adInteger,
    adParamReturnValue))
    cmd.Parameters.Append (cmd.CreateParameter("AccountNo", adInteger,
    adParamInput,, nAccountNumber))

    '//SET THE REDCORDSET OBJECT
    Set rs = cmd.Execute

    response.Write("retval: " & cmd.Parameters("RetVal"))


    STORED PROCEDURE:
    CREATE PROCEDURE [dbo].[p_FINAPRO_100Form]
    AccountNo int

    AS

    Declare AccountId int
    Declare AgentId int
    Declare InsuredId int
    Declare AccountNumber varchar(10)
    Declare HowToSend int
    Declare DueDate datetime
    Declare SiteId int
    Declare PaymentNumber int
    Declare MonthlyPayment money
    Declare PolicyNumbers varchar(255)
    Declare ActivityId int


    /* Select account information*/

    select
    AccountId = Id,
    AgentId = AgentId,
    InsuredId = InsuredId,
    AccountNumber = AccountNumber,
    DueDate = NextPaymentDuedate,
    SiteId = SiteId,
    MonthlyPayment = CurPaymentAmount

    from Accounts
    where AccountNumber = AccountNo

    /* insert for the insured */

    Begin
    insert into NoticeActivity
    ( AccountId,
    SiteId,
    NoticeDate,
    DateSent,
    SendStatus,
    SendTo,
    HowToSend,
    NoticeType,
    InsuredId,
    AgentId,
    AccountNumber,
    DueDate,
    MonthlyPayment)
    values
    ( AccountID,
    SiteID,
    GETDATE(),
    GETDATE(),
    2,
    1,
    1,
    100,
    InsuredId,
    AgentId,
    AccountNumber,
    DueDate,
    MonthlyPayment)

    return identity
    End
    GO

    Any help would greatly be appreciated.

    Thanks


    Scott Guest

  2. #2

    Default Re: Return Value from Stored Procedure

    You should also notice that RETURN statement only allows an INT value. I
    guess your IDENTITY may contain a BIGINT value in a quite populated table.


    --

    *******************************
    Osvaldo Bisignano
    Buenos Aires, Argentina
    ICQ#96485948




    "Scott" <com> escribió en el mensaje
    news:phx.gbl... 
    to 


    Osvaldo Guest

  3. #3

    Default Re: Return Value from Stored Procedure

    Scott,

    RETURN statement in stored procedures is used to exit from code and/or to return status value.
    If you want your stored procedure to return output value then use output parameter instead of return.


    Change your SP to

    CREATE PROCEDURE [dbo].[p_FINAPRO_100Form]
    AccountNo int , NewValue int output

    AS
    ........
    ........
    ........



    "Scott" <com> wrote in message news:phx.gbl... 
    praveen Guest

  4. #4

    Default Re: Return Value from Stored Procedure

    To add to what the others have said:
    You could also return a recordset:
    select identity
    ....but I would prefer the output parameter in this case.


    raydan Guest

Similar Threads

  1. Cannot return a value from a stored procedure
    By wittsdd in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: September 19th, 04:20 PM
  2. Trouble getting stored procedure return value!?
    By maxfraser in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 17th, 09:04 PM
  3. Can't get return Value from Stored Procedure
    By Mark Forsberg in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 15th, 07:14 PM
  4. Replies: 15
    Last Post: July 27th, 05:35 AM
  5. Getting Return Value of Stored Procedure
    By Vipul Pathak in forum ASP Database
    Replies: 8
    Last Post: July 30th, 01:51 PM

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