• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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

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
0
purplesoup
Asked:
purplesoup
1 Solution
 
SQL_SERVER_DBACommented:
declare it and set it
0
 
purplesoupAuthor Commented:
when I try SET I get incorrect syntax - what syntax should I use to set it?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
put a select statement as the last one


SELECT 'Value' as RetVal
0
 
Scott PletcherSenior DBACommented:
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
0
 
purplesoupAuthor Commented:
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?

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now