Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Get return code and output from stored procedure using transact sql

    Hi all,

    I have a stored procedure like this:

    *************************************
    create proc get_id
    @id int
    AS

    SELECT name from table where key=@id

    return -999 /*SUCCESS*/
    **************************************

    I would like to create another stored procedure like this:
    ***************************************
    create proc process_id
    AS
    declare @ret int

    BEGIN
    exec @ret = get_id @id

    END
    *****************************************

    This only gives the return code -999 from get_id. How do I get the name returned as part of the select query in the get_id procedure?

    Please note that both the stored procedures reside on the same database.

    Thanks
    chakde

  2. #2
    Join Date
    May 2011
    Posts
    28
    Hi,
    You can use OUT parameter in stored procedure to return multiple values. Here both return codes & name can be return using OUT parameter.
    Example:
    Code:
    exec myproc @ret = @ret out, @name  = @name  out
    You can refer to the link below.
    http://manuals.sybase.com/onlinebook...43833;pt=42965
    Last edited by pradyut.dhara; 01-25-12 at 02:36. Reason: URL insert

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Thanks for the reply. But that would mean, the original sp needed to be modified which is not possible in my case since it is considered legacy and changing it would break existing applications.

    Are there any other mechanisms to retrie the rc and values returned from the SP? For example, I came accross temp tables but could not get it to work.

    Any thoughts?

    Thanks

  4. #4
    Join Date
    May 2011
    Posts
    28

    Get return code and output from stored procedure using transact sql

    Hi,
    Even if you are going to use tempdb how you are going to store the name without modifying the stored procedure.
    Does not it print the name in console?You can get the value from there.

Posting Permissions

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