Returning output parameter from stored procedure

Question

I created a stored procedure based on the example from here on SO. However, I am getting an error. What am I missing here?

CREATE PROCEDURE SFT.usp_GetPrefix
(@p_table  VARCHAR(30), 
 @p_prefix VARCHAR(3) OUTPUT
)
AS
    BEGIN
        DECLARE @lv_prefix VARCHAR(3);

        SELECT @lv_prefix = SUBSTRING(@p_table, 1, 3);

        IF(@lv_prefix = 'MAC')
            BEGIN
                SET @p_prefix = @lv_prefix;
                RETURN;
            END;
        ELSE
            SET @p_prefix = 'UNK';
            RETURN;
    END;

This is how I am executing:

EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE';

Msg 201, Level 16, State 4, Procedure SFT.usp_GetPrefix, Line 0 [Batch Start Line 6] Procedure or function 'usp_GetPrefix' expects parameter '@p_prefix', which was not supplied.

Also tried:

EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT

Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@p_prefix".

`declare @TheResult as VarChar(3); execute SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix = @TheResult output; select @TheResult as TheResult;`. — HABO, May 28 '20 at 17:50

score 1 · Accepted Answer · answered May 28 '20 at 16:34

1

You need to pass parameters, as neither have default values. As @p_prefix is an OUTPUT parameter, in order to consume that value, you need to declare a variable to pass to the parameter:

DECLARE @p_prefix varchar(3);
EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT;

--PRINT @p_prefix; --Or whatever you're going to do with the value.

As a side note, you can make your Procedure considerably shorter:

CREATE PROCEDURE SFT.usp_GetPrefix @p_table  VARCHAR(30), @p_prefix VARCHAR(3) OUTPUT AS
BEGIN

    SET @p_prefix = CASE LEFT(@p_table,3) WHEN 'MAC' THEN 'MAC' ELSE 'UNK' END;

END

answered May 28 '20 at 16:34

Thom A

88,727
11
45
75

Larnu, does that mean that I should declare the @p_prefix variable within the stored procedure as well? BTW, thanks for providing the short code. The code I posted is just a an example to test out the output parameter. :-) – Julaayi May 28 '20 at 16:43
*"does that mean that I should declare the @p_prefix variable within the stored procedure as well?"* `@p_prefix` is a parameter in your Procedure, @Julaayi , you can't declare it inside as a variable, as you have a parameter with the same name. – Thom A May 28 '20 at 16:47
Apologies, it's been a long day and I didn't realize what I posted. :D – Julaayi May 28 '20 at 19:38

Returning output parameter from stored procedure

1 Answers1