CODEWITHSUNDEEP
sqlt-sqlstored-procedures
user544079
user544079

Reputation: 16629

OUTPUT parameter returns a single row and not the entire column

I have a stored procedure as

create procedure p1
    @id  INT,
    @name varchar(20) OUTPUT
as
begin
    select @name = name 
    from table1 
    where id = @id;
end

on executing it, I simply get 1 single row as a value.

However if I do not use the output parameter as

create procedure p1
   @id  INT
as
begin
    select name from table1 where id = @id;
end

I get the entire column. Note: In my table a single id can have multiple values, so ideally it should return > 1 values even with the OUTPUT parameter.

Why do I just get 1 row when using the OUTPUT parameter.

Upvotes: 0

Views: 270

Answers (1)

JC Ford
JC Ford

Reputation: 7066

You actually don't get "one row" you get one scalar value via the output parameter because that's what you declared its type to be. varchar is a scalar value type. If you want a row set you don't need to use a parameter for it.

Upvotes: 2

Related Questions