SQL Server Result Set Into Output Parameters?

Question

I had a sproc that executed a select statement, result was a single column and single row that I assigned to an output parameter.

Now the sproc has to grow to return two columns from a single row result set.

What is the cleanest way to keep the single select (just making it return two columns instead of one) and have two row values assigned to seperate (output) variables?

Thanks.

Martin Smith · Accepted Answer · 2011-11-02 16:12:40Z

1

You can just declare 2 output parameters and assign to both from the same select

select @foo = foo, @bar=bar ...

answered Nov 2, 2011 at 16:12

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

Comments

user596075 · Accepted Answer · 2011-11-02 16:09:36Z

1

If you are looking for a return value that isn't scalar (i.e. a set of data), then you won't really be able to use a stored procedure. A table variable can be READONLY in a stored procedure.

I'd recommend converting your stored procedure logic to a Table-Valued Function. This will be fine as long as you aren't doing any DML statements inside the function. But as per your description, it seems like it's just a SELECT.

Also, the benefit of a function is it can be queried inline.

answered Nov 2, 2011 at 16:09

user596075

Collectives™ on Stack Overflow

SQL Server Result Set Into Output Parameters?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related