Prev: mysql file format
Next: How to execute SP that returns a select set from another SP
From: Omid Golban on 6 Jul 2008 13:19
Consider a simple stored procedure that returns multiple result sets:
CREATE PROCEDURE MyProcedure
@Params ...
AS
BEGIN
...
SELECT columns ... FROM ...
...
SELECT columns ... FROM ...
END

Is there any way to identify each of these result sets with a name? Something similar to the column alias?
For example:
(SELECT columns ...FROM ...) AS SomeName

Thank you,
Omid
From: Mike John on 6 Jul 2008 14:18
Not totally sure why you want to do this but..

Select 'somename' as tablename,* from ...
Select 'anothername' as tablename,* from ..

Of course without using the * in production code.

Mike John

"Omid Golban" <ogolban(a)cox.net> wrote in message
news:63E1BA04-2997-458D-B5EC-82E1A58EFE72(a)microsoft.com...
> Consider a simple stored procedure that returns multiple result sets:
> CREATE PROCEDURE MyProcedure
> @Params ...
> AS
> BEGIN
> ...
> SELECT columns ... FROM ...
> ...
> SELECT columns ... FROM ...
> END
>
> Is there any way to identify each of these result sets with a name?
> Something similar to the column alias?
> For example:
> (SELECT columns ...FROM ...) AS SomeName
>
> Thank you,
> Omid

From: William Vaughn (MVP) on 6 Jul 2008 15:24
No rowset as returned by a SELECT is identified by "name". Multiple
resultsets are handled by "ordinal" -- the order in which they are returned.
I would not attempt to cobble something together to do this as no one will
understand why you're doing it.

"Omid Golban" <ogolban(a)cox.net> wrote in message
news:63E1BA04-2997-458D-B5EC-82E1A58EFE72(a)microsoft.com...
> Consider a simple stored procedure that returns multiple result sets:
> CREATE PROCEDURE MyProcedure
> @Params ...
> AS
> BEGIN
> ...
> SELECT columns ... FROM ...
> ...
> SELECT columns ... FROM ...
> END
>
> Is there any way to identify each of these result sets with a name?
> Something similar to the column alias?
> For example:
> (SELECT columns ...FROM ...) AS SomeName
>
> Thank you,
> Omid

--
William Vaughn (MVP)
Author, Mentor, Consultant, Dad
Redmond, WA USA
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

From: Eric Isaacs on 6 Jul 2008 15:59
I agree with Bill. You shouldn't need this. If you want your rowsets
to be more self documenting, don't have your procedures return more
than one rowset. Use multiple procedures instead. Alternatively, you
can use an enumeration or constants in your code that document the
ordinal value of the returned rowset. These will need to be kept in
sync with the rowsets, but so would any ordinal values in your code,
so using constants or an enumeration within your code would make the
maintenance easier and would probably provide you what you're asking
for.

-Eric Isaacs
 | 
Pages: 1
Prev: mysql file format
Next: How to execute SP that returns a select set from another SP