Execute a stored procedure from another, and return two variables

Question

I have a stored procedure called clients with 3 parameters: the first one for user input, and the last two are OUTPUT parameters.

This is the code:

CREATE PROCEDURE clients
     (@name NVARCHAR(100),
      @id_client int OUTPUT,
      @messg varchar(1) OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
    BEGIN TRAN
        IF NOT EXISTS (SELECT name FROM client WHERE name = @name)
        BEGIN
            INSERT INTO client(name) VALUES (@name);

            SET @id_client = SCOPE_IDENTITY();
            SET @messg = 'o'

            COMMIT TRAN
        END
        ELSE
        BEGIN
            SELECT @id_client = id_client 
            FROM client 
            WHERE name = @name;

            SET @messg = 'o'

            COMMIT TRAN
        END
    END TRY
    BEGIN CATCH
        SET @messg = 'e'
        ROLLBACK TRAN
    END CATCH
END

I need to call this stored procedure from another the second one is called updateS, and I'm trying the following:

CREATE PROCEDURE updateS
     (@clientname VARCHAR(100))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @id INT;
    DECLARE @msg VARCHAR(1);

    EXEC clients @clientname, @id, @msg; --Problem here to retrieve the id
END

This stored procedure has a parameter for the name of the client, but I need to retrieve the id of the client, but it doesn't work as I'm trying.

Basically I need to get the id and use it in the second stored procedure.

Any question post on comments.

score 3 · Accepted Answer · answered Nov 08 '16 at 00:52

3

You need to specify OUTPUT when you execute the stored procedure as well as when you define it:

EXEC clients @clientname, @id OUTPUT, @msg OUTPUT;

answered Nov 08 '16 at 00:52

Gordon Linoff

1,242,037
58
646
786

Oh! It's just what I needed. Thanks a lot. – TimeToCode Nov 08 '16 at 00:59

score 1 · Answer 2 · answered Nov 08 '16 at 00:53

1

Did you miss the output keyword while passing the parameter to the stored procedure? Moreover, I would change the clients stored procedure and make the parameter as char(1) rather like @messg char(1) OUTPUT

answered Nov 08 '16 at 00:53

Rahul

76,197
13
71
125

I appreciate your answer, but why i need use `char` instead `varchar`? It's for perfomance or for another reason? This is my first time using SQL Server. – TimeToCode Nov 08 '16 at 01:01
1

@TimeToCode, Yes it's for performance. Since your content is fixed (1 character only) you will gain better performance. See http://stackoverflow.com/questions/1885630/whats-the-difference-between-varchar-and-char – Rahul Nov 08 '16 at 01:06

Execute a stored procedure from another, and return two variables

2 Answers2