2

I get the exception

"The formal parameter "@param1" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output"

I'm pretty sure I got everything in the correct order.

Is anything wrong with this stored procedure?

CREATE PROCEDURE spHello
    @param1 INT, 
    @param2 INT OUT 
AS 
BEGIN 
    SET @param2 = @param1 + 2 
    RETURN 1 
END

I call it using this:

DECLARE @return_value int, @param2out  int 

EXEC @return_value = spHello 1, @param2out  OUTPUT 

SELECT @param2out  as N'@param2' 
SELECT 'Return Value' = @return_value
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eiman Mission
  • 43
  • 1
  • 5
  • 1
    Show us the code that's calling the proc and raising the error. The error suggests you've specified `OUTPUT` for `@param1` whereas that can only be done for `@param2` (matching the proc declaration). – Dan Guzman Jun 09 '18 at 14:48
  • @DanGuzman I edited the original question. Thanks Dan! – Eiman Mission Jun 09 '18 at 14:54
  • 1
    Are you sure you are calling the procedure you think you are calling? Do you have `spHello` in other schemas or databases? – GSerg Jun 09 '18 at 15:03
  • If you only need to return one value, I would recommend that you use a user-defined function instead of a stored procedure. – Brian Jun 09 '18 at 15:23
  • 1
    Why do you need to use `RETURN` and `OUTPUT`? – Thom A Jun 09 '18 at 15:36

1 Answers1

4

Your code looks correct: Demo.

I guess the problem is with parameter list order. To avoid confusion I would use named parameters:

DECLARE @return_value int, @param2out  int;
EXEC @return_value = spHello @param1 = 1, @param2 = @param2out OUTPUT;

SELECT @param2out AS [@param2],
       @return_value AS [Return Value];

DBFiddle Demo

It's a good practice to end each statement with semicolon.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275