TimeToCode TimeToCode - 25 days ago 5
SQL Question

Execute a Stored Procedure from another, and return two variables

I have a stored procedure called

clients
, this sp have 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 SP 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 retrive the id
END


This SP have a parameter for the name of the client, but i need to retrive 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 SP.

Any question post on comments.

Answer

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;
Comments