Return a value from stored procedure to calling application

Discussion in 'ASP .Net' started by Paul, Jan 19, 2004.

  1. Paul

    Paul Guest

    Hi,

    In SQL Books Online in the section on @@Error it gives the following
    example:

    -- Execute the INSERT statement.
    INSERT INTO authors
    (au_id, au_lname, au_fname, phone, address,
    city, state, zip, contract) values
    (@au_id,@au_lname,@au_fname,@phone,@address,
    @city,@state,@zip,@contract)

    -- Test the error value.
    IF @@ERROR <> 0
    BEGIN
    -- Return 99 to the calling program to indicate failure.
    PRINT "An error occurred loading the new author information"
    RETURN(99)
    END
    ELSE
    BEGIN
    -- Return 0 to the calling program to indicate success.
    PRINT "The new author information has been loaded"
    RETURN(0)
    END
    GO

    How do I access the value returned by the RETURN statement (i.e. 99 or
    0) in my asp application that called the stored proc.

    Sometimes rather than just return an integer signifying success or
    failure I've seen examples where the id of the newly added item is
    returned on success and perhaps -1 if the operation fails. These
    examples make use of ouput parameters to achieve this. If the
    operation succeeds then then the output parameters value is set to the
    new id and this is accessed from the calling application.

    E.g.

    IF @@ERROR <> 0
    BEGIN
    -- Return -1 to the calling program to indicate failure.
    PRINT "An error occurred loading the new author information"
    SELECT @MyOuptputParameter = -1
    END
    ELSE
    BEGIN
    -- Return id to the calling program to indicate success.
    PRINT "The new author information has been loaded"
    SELECT @MyOuptputParameter = @@IDENTITY
    END

    Why go to this trouble if you can use the RETURN statement?
     
    Paul, Jan 19, 2004
    #1
    1. Advertising

  2. Hi,

    If you want to get the return value in your .NET application, all you have to do is to add a Parameter in the SqlCommand's Parameters collection. This SqlParameter object must have the ParameterDirection property set to ParameterDirection.ReturnValue.
    After executing the command, you can access the parameter like an output parameter:

    Hope this helps,

    Ramiro Calderon
     
    =?Utf-8?B?UmFtaXJvIENhbGRlcm9u?=, Jan 19, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neo Chou
    Replies:
    1
    Views:
    776
  2. Nick
    Replies:
    8
    Views:
    3,270
  3. philip
    Replies:
    2
    Views:
    11,572
    bruce barker \(sqlwork.com\)
    Aug 10, 2006
  4. =?Utf-8?B?QmlzaG95?=
    Replies:
    1
    Views:
    305
    Mark Fitzpatrick
    Dec 24, 2006
  5. Replies:
    1
    Views:
    389
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Jun 18, 2007
Loading...

Share This Page