CODEWITHSUNDEEP
sqlsql-servert-sql
user544079
user544079

Reputation: 16629

display multiple output parameters in T-SQL stored procedure

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?

Upvotes: 0

Views: 1299

Answers (1)

suff trek
suff trek

Reputation: 39777

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

Upvotes: 2

Related Questions