0

I am calling a stored procedure (that I can't modify) that returns a single value via a SELECT at the end of the procedure. I need to use that value in my own procedure. How can I get that value?

Here is an example of the procedure I am calling that I can not modify:

CREATE PROCEDURE [dbo].[SP_poorlywritten] (
....
)
BEGIN
    ....
    SELECT @lastkey;
    RETURN (0);
END

And here is what I am trying to do, but it doesn't work because this gets the return value, and what I need is that SELECT value:

exec @next_key = SP_poorlywritten 'tablename',1;

How can I store the first column, first row value from a store procedure?

Dale K
  • 25,246
  • 15
  • 42
  • 71
longneck
  • 11,938
  • 2
  • 36
  • 44
  • 1
    `RETURN` is for error/status, it is [not meant for data](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output) (which is why it only supports `int` - if your column were `bigint` or any non-integer data type, you wouldn't be able to use it). – Aaron Bertrand Mar 04 '22 at 19:42

1 Answers1

2

If you cannot modify the existing stored procedure, you will not be able to utilize the RETURN as you would like to.

An alternative may be to insert the output of the procedure's select statement into a temp table, and then query that directly to populate a variable.

That would look like this.

CREATE TABLE #tmp
(
   LastKey INT
)

INSERT INTO #tmp
EXEC [dbo].[SP_poorlywritten]

See this existing post for more details on how you might accomplish this.

As Aaron Bertrand pointed out, RETURN is for error/status. If you were able to modify the stored procedure, you would want to utilize an output parameter instead of RETURN. This is how you would do that.

CREATE PROCEDURE proc_name
    @Output int OUTPUT
AS
    <do some stuff>

    SET @Output = <some_value>
GO

DECLARE @Output int

EXEC proc_name @Output = @Output
Brendan McCaffrey
  • 386
  • 1
  • 11