Getting a SELECT value from a stored procedure into a variable of another stored procedure.

Posted on 2007-10-16
Last Modified: 2010-03-19
I have a stored procedure which I can't change that returns a value with a SELECT statement.

I need to get this return value into a variable from another stored procedure.

How can I do this.

Note - I am not wanting to know how to get a return value using the RETURN statement or an OUTPUT parameter, I want to know how to get a value from a SELECT statement run in a stored procedure
Question by:purplesoup
    LVL 16

    Expert Comment

    declare it and set it

    Author Comment

    when I try SET I get incorrect syntax - what syntax should I use to set it?
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    put a select statement as the last one

    SELECT 'Value' as RetVal
    LVL 68

    Accepted Solution

    If there's only one SELECT in the proc (or if it's the first SELECT, I think), you can capture the results into a temp table, assuming you know the structure of the table.  For example:

    CREATE TABLE #results (
        ...table def matching results from stored proc...,

    INSERT INTO #results
    EXEC storedProc1

    EXEC storedProc2  --sp2 can use #results

    Author Comment

    This isn't clear - let me give you the details.

    I have a procedure for returning a counter for a table,

    exec sp_get_counter 'Activity'

    this returns the current counter for the table 'Activity' as a SELECT statement (i.e. running this in Query Analyzer displays the counter in the results pane).

    I need to call this from within another stored procedure, so it looks something like this:

    declare @seq int

    set @seq = sp_get_counter 'Activity'

    but the above syntax doesn't work - what do I need to enter to set the @seq variable with the value returned from the sp_get_counter stored procedure?


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now