display multiple output parameters in T-SQL stored procedure

Question

I want to display 2 tables of 1 column each as my output in the stored procedure

Defined as

create procedure p1
        @name     varchar(20) OUTPUT,
        @company  varchar(20) OUTPUT
As

BEGIN
      select @name = t1.name from table1 t1;

      select @company = t2.company from table2; 

END

Executed as

declare @name varchar(20), @company varchar(20)

exec dbo.p1 @name = @name, @company = @company

select @name as 'Name', @company as 'Company'

However, this just displays a single row . What am I doing wrong?

Comfortably Numb · Accepted Answer · 2013-07-31 18:02:53Z

2

If you want to display those values as a 1 column, 2 rows - use UNION:

select @name as 'Name'
UNION ALL
select @company

Note that both values will display under same column name 'Name'

If you want to display strings 'Name' and 'Company' as well you will have to assure order of rows by another column:

select 'Name' as Info, 0 as Sort
UNION ALL
select @name, 1
UNION ALL
select 'Company', 2
UNION ALL
select @company, 3
Order by Sort

answered Jul 31, 2013 at 18:02

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

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

Collectives™ on Stack Overflow

display multiple output parameters in T-SQL stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related