Get scalar value from SELECT statement in stored proc, from within a stored proc

Question

I know the preferred method for returning scalar values from stored procs is either using RETURN or an OUTPUT parameter. But lets say that I have a stored proc that returns the value using a select statement:

CREATE PROC spReturnNumber AS

SELECT 1

Is it possible to get this value from within another stored proc?

CREATE PROC spCheckNumber AS

EXEC spReturnNumber -- <-- get the return value here?

Clarification: I need a solution that doesn't require using an OUTPUT parameter, or using RETURN to return the value.

Thanks in advance.

Answer 1

You could use insert-exec to store the result of a stored procedure in a table:

declare @t table (col1 int)
insert @t exec spReturnNumber
return (select col1 from @t)

The definition of the table has to match the result set of the stored procedure.

Answer 2

Use an OUTPUT parameter instead of (or in addition to, if this procedure is used by other applications) the SELECT.

ALTER PROCEDURE dbo.spReturnNumber
    @Number INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @Number = 1;
    SELECT @Number;
END
GO

CREATE PROCEDURE dbo.spCheckNumber
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Number INT;
    EXEC dbo.spReturnNumber @Number = @Number;
    SELECT @Number;
END
GO

If you can't change the original procedure, but you know its output will remain static, you could use a #temp table.

CREATE PROCEDURE dbo.spCheckNumber
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #n(i INT);
    INSERT #n(i) EXEC dbo.spReturnNumber;

    DECLARE @Number INT;
    SELECT @Number = i FROM #n;
END
GO