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'.

Damien_The_Unbeliever · Accepted Answer · 2013-07-25 06:54:15Z

14

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)

edited Jul 25, 2013 at 6:54

answered Jul 25, 2013 at 6:03

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

2 Comments

NateJ Over a year ago

This should be marked as the answer. I would also add, to avoid confusion, that in the "caller" code you should name your variables differently than the parameters. Something like this: "exec dbo.p1 <at>id=2, <at>name = <at>outName, <at>company = <at>outCompany". PS: forgive the silly substitution, I didn't remember that comments couldn't contain code or >1 at-symbols.

NateJ Over a year ago

Okay fine, I read the help... exec dbo.p1 @id=2, @name=@outName, @company=@outCompany Thanks for the push!

Joseph Woodward · Accepted Answer · 2015-08-21 14:30:58Z

3

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

edited Aug 21, 2015 at 14:30

Joseph Woodward

9,2915 gold badges48 silver badges66 bronze badges

answered Jul 25, 2013 at 6:48

Low Chee Mun

6101 gold badge4 silver badges9 bronze badges

Comments

tsohtan · Accepted Answer · 2013-07-25 07:00:46Z

2

change to this will work

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

answered Jul 25, 2013 at 7:00

tsohtan

8502 gold badges13 silver badges37 bronze badges

Comments

Satish Singh · Accepted Answer · 2014-01-13 08:18:13Z

0

Here is a working code for your question.

Please try in this way. It will work fine.

Procedure

alter procedure my_pro
@id int,
@name varchar(30) output,
@email varchar(50) output
as
begin
select @name=Name,@email=EmailID from Users where id=@id
end

Execute

declare @Newname varchar(40)
declare @Newemail varchar(40)
exec my_pro 2,@Newname output, @Newemail output
select @Newname 'Name', @Newemail 'Email'

answered Jan 13, 2014 at 8:18

Satish Singh

2,3493 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

T-SQL Stored procedure with OUTPUT parameter giving an error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related