T-SQL Stored procedure with OUTPUT parameter giving an error

Question

My stored procedure is defined as

create procedure p1
(
    @id INT,
    @name varchar(20) OUTPUT,
    @company varchar(20) OUTPUT
)
AS
     BEGIN
        select @name = name, @company = company from table1 where id = @id;
     END
GO

I call it using

DECLARE @name varchar(20), @company varchar(20), @id INT;
exec dbo.p1 @id=2, @name OUTPUT, @company OUTPUT;
select @name AS 'NAME', @company AS 'COMPANY'

However I get an error

'Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Answer 1

Follow the instructions in the error message:

DECLARE @name varchar(20), @company varchar(20), @id INT;
exec dbo.p1 @id=2, @name = @name OUTPUT, @company = @company OUTPUT;
select @name AS 'NAME', @company AS 'COMPANY'

---

Alternative - don't name parameter 1:

DECLARE @name varchar(20), @company varchar(20), @id INT;
exec dbo.p1 2, @name OUTPUT, @company OUTPUT;
select @name AS 'NAME', @company AS 'COMPANY'

---

From EXECUTE:

When used with the @parameter_name=value form, parameter names and constants do not have to be supplied in the order in which they are defined in the module. However, if the @parameter_name=value form is used for any parameter, it must be used for all subsequent parameters.

(Because, obviously, the first usage might be defining any parameter, so it can no longer assume that any subsequent ones should be assigned in order)

Answer 2

Brother, I am not sure what you're trying to select but maybe you can try the query below:

CREATE PROCEDURE p1
(
@id INT,
@name varchar(20) OUTPUT,
@company varchar(20) OUTPUT
)
AS
 BEGIN
Set @name = 'name'
Set @company = 'company'
    select @name , @company from table1 where id = @id;
 END
GO