CODEWITHSUNDEEP
sql-server-2008
Snowy
Snowy

Reputation: 6132

SQL Server Result Set Into Output Parameters?

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.

Upvotes: 1

Views: 1001

Answers (2)

Martin Smith
Martin Smith

Reputation: 453608

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

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

Upvotes: 1

user596075
user596075

Reputation:

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.

Upvotes: 1

Related Questions