Add input parameter to stored procedure and passing it into an output variable?

Question

I currently have a stored procedure I'm working on that's supposed to output a message when something fails.

ALTER PROCEDURE---
@in_id INT
@msg NVARCHAR(30) = OUTPUT

The procedure variables are structured like this where one of them is an input parameter and the msg is an output.

The idea is that it uses that input parameter of an ID which works as an identification that changes the message based on the ID so the end of the sproc looks something like:

SET @msg = usf_change_message(@id, @msg)

I get an error stating that the sproc is trying to treat @msg as an input variable when in actuality it's an OUTPUT param? Can someone help me with this issue?

Akina · Accepted Answer · 2022-03-25 10:02:23Z

1

You use your SP as function which is obviously wrong - SP does not return a value.

You must use:

CALL usf_change_message(@id, @msg);
SELECT @msg;

Or you may convert SP to UDF and use your SELECT query (but in this case @msg in UDF parameters is excess and should be removed).

answered Mar 25, 2022 at 10:02

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Add input parameter to stored procedure and passing it into an output variable?

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