Get result parameter of stored procedure in exec clause

Question

I have a stored procedure that returns multiple parameters:

CREATE PROCEDURE [dbo].[TestSP]
    @Test1 INT
    , @Test2 UNIQUEIDENTIFIER

    --some inserts and alters here

    SELECT TOP 1
          @Parameter1 AS Design
        , @Parameter2
        , @Parameter3
    FROM Table

I want to use EXEC into another stored procedure and get ONLY @Parameter1 (Design)

So I want to get @Parameter1 after EXEC stored procedure, so I think about OUTPUT, but it doesn't work, is there a way to achieve this?

CREATE PROCEDURE [dbo].[SecondStoredProcedure]
    @Sender1 INT
    , @Sender2 UNIQUEIDENTIFIER

    DECLARE @ReturnedParameter1 INT

    EXEC [dbo].[TestSP] @Test1 = @Sender1, @Test2 = @Sender2 OUTPUT [Design] 
    INTO @ReturnedParameter1 

    SELECT @ReturnedParameter1

Its really not clear what you are asking... Please show the full definition on each SP, and clarify what you mean by return, because you are selecting in your example, not returning. — Dale K
– Dale K, Commented Apr 1, 2020 at 22:59
Also please read the official docs because they will most likely answer your questions. — Dale K
– Dale K, Commented Apr 1, 2020 at 22:59
And where are @Parameter1, @Parameter2 & @Parameter3 defined? — Dale K
– Dale K, Commented Apr 1, 2020 at 23:14

David Browne - Microsoft · Accepted Answer · 2020-04-01 23:17:31Z

1

That procedure creates a resultset, and has no output parameters. You can capture a resultset with insert into ... exec, like this:

use tempdb
go

CREATE PROCEDURE [dbo].[addDesign]
             @Test1                      INT
            ,@Test2                       UNIQUEIDENTIFIER
as
begin
--some inserts and alters here

    SELECT 
          1 AS Design
        , 2 as Foo
        , 3 as Bar

end
go

declare @rv table(Design int, Foo int, Bar int)

declare @test2 uniqueidentifier = newid()

insert into @rv
exec addDesign 1, @test2

declare @design int = (select Design from @rv)

select @design

answered Apr 1, 2020 at 23:17

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2020-04-01 23:20:33Z

I suggest using an output parameter as explained in the official docs.

Here is how your code might look in this case:

use tempdb
go

create procedure [dbo].[addDesign]
(
  @Test1 int
  , @Test2 uniqueidentifier
  , @Design int out
)
as
begin
  set nocount on;

  --some inserts and alters here

  -- Set the return parameter
  set @Design = @Parameter1;

  -- Select the return results
  SELECT TOP 1
    @Parameter1
    , @Parameter2
    , @Parameter3
  FROM dbo.MyTable;

  -- Return status code, proc ran OK
  return 0;
end
go

create procedure [dbo].[SecondStoredProcedure]
(
  @Sender1 int
  , @Sender2 uniqueidentifier
)
as
begin
  set nocount on;

  declare @ReturnedParameter1 int;

  exec dbo.TestSP @Test1 = @Sender1, @Test2 = @Sender2, @Design = @ReturnedParameter1; 

  select @ReturnedParameter1;

  -- Return status code, proc ran OK
  return 0;
end
go

Note: This demonstrates the 3 ways information can be returned from a stored procedure, the result code (only 1), output parameters (0-N) and result sets (0-N).

Collectives™ on Stack Overflow

Get result parameter of stored procedure in exec clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related