Store Stored Procedure Result With Multiple Parameters

Question

I have two Stored Procedures which receives multiple parameters.

usp_stored_proc1 which returns a uniqueidentifier at the end of the stored procedure: SELECT column1

usp_stored_proc2 which should get the column1 as a parameter from the usp_stored_proc1

Here is my code:

EXEC usp_stored_proc1 'Test1', 'Para1', 'Para2'
EXEC usp_stored_proc2 SELECT column1, 'Para3', 'Para4'

and here is my attempt to pass the usp_stored_proc1 as a parameter:

 EXEC usp_stored_proc2 usp_stored_proc1, 'Test1', 'Para1', 'Para2', 'Para3', 'Para4'

but failed.

(1) Tag your question with the database you are using. (2) If you want to return a value, use either a user-defined function or an OUTPUT parameter. SELECT doesn't return anything. — Gordon Linoff
– Gordon Linoff, Commented Sep 14, 2021 at 10:46
You cannot pass a "stored procedure" as a parameter to another stored procedure. And the only way to access a resultset produced by a stored procedure in tsql is to insert it into a table using the form <insert tbl (...) exec proc ...>. You will need many more steps to accomplish this without changing how your first procedure returns information to the caller. — SMor
– SMor, Commented Sep 14, 2021 at 12:15

marc_s · Accepted Answer · 2021-09-15 04:46:13Z

1

As @GordonLinoff said in comments, you can use an OUTPUT parameter in your stored procedure like this:

CREATE PROCEDURE usp_stored_proc1
    @FirstParam NVARCHAR(50),
    --rest of parameters
    @Column1 UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    --some stuff with input parameters
    
    SET @Column1 = NEWID()
END

And execute stored procedures like this:

DECLARE @Column1 UNIQUEIDENTIFIER

EXEC [dbo].usp_stored_proc1 'test', @Column1 OUTPUT

EXEC [dbo].usp_stored_proc2 @Column1,'param1'

edited Sep 15, 2021 at 4:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 14, 2021 at 11:06

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Store Stored Procedure Result With Multiple Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related