How to insert a field of a procedure result in a variable?

Question

I have a procedure dbo.pX that returns a table with 1 field named id and an INT in it, that can be equal to 0, or another positive integer. I need to insert this id in a variable inside a procedure named dbo.pY, to verify if the id is greater than 0.

Procedure dbo.pX:

CREATE PROCEDURE dbo.pX
    @param1 VARCHAR(30)
AS
    DECLARE @id INT;
    -- Some code to change the @id
    SELECT @id AS 'id';
GO

What I tried to do in dbo.pY:

CREATE PROCEDURE dbo.pY
    @param1 VARCHAR(30)
AS
    DECLARE @ret INT;
    SET @ret = (EXECUTE dbo.pX 'something');
    IF ( @ret > 0 ) BEGIN
        -- Some code that uses @ret
    END
GO

Any tips or hints to help me find a way to solve it?

Obs: I can't change the procedure dbo.pX.

either you use a scalar function to return or a stored script with output option — RoMEoMusTDiE, Jul 16 '17 at 23:14
Stored procedure Output parameters: https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx . INSERT INTO EXEC: https://stackoverflow.com/questions/12686175/sql-server-insert-into-execute-statement — Alex, Jul 17 '17 at 00:22

score 0 · Accepted Answer · answered Jul 20 '17 at 02:42

As I know the fields that the procedure was bringing me, and I couldn't change that procedure, I solve it like this:

CREATE PROCEDURE dbo.pY
    @param1 VARCHAR(30)
AS
    -- insert the procedure table inside a temporary table
    DECLARE @temp TABLE (id INT);
    INSERT INTO @temp EXECUTE dbo.pX 'something';

    -- insert id that the table brings in a variable
    DECLARE @ret INT;
    SELECT @ret = id FROM @temp;

    IF ( @ret > 0 ) BEGIN
        -- Some code that uses @ret
    END
GO

Problem solved :)

How to insert a field of a procedure result in a variable?

1 Answers1