Hi all,

I want to create a stored procedure which will extract the "GroupName"
from the record returned by "sp_helpuser". In order to do this I need
to execute "sp_helpuser" which returns the entire record. I want to
just extract the "GroupName" from the record and return it to my
application. How do I go about this?

Thanks in advance,

Alvin

Alvin Sebastian (asebastian@cmri.usyd.edu.au) writes:
> I want to create a stored procedure which will extract the "GroupName"
> from the record returned by "sp_helpuser". In order to do this I need
> to execute "sp_helpuser" which returns the entire record. I want to
> just extract the "GroupName" from the record and return it to my
> application. How do I go about this?

Either you access sysusers directly, you can use the INSERT EXEC construct:

INSERT #temp (...)
EXEC sp_helpuser

You need to create #temp so that it agrees with the output from sp_helpuser.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks Erland.

By the way, I forgot to mention in the first post that I'm only
interested in the "GroupName" of the currently logged-on user so the
stored procedure will be returning a single string value only and not
a table. How should the stored procedure return this single value from
the record returned by "sp_helpuser"?


Alvin




Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93BF646E19EF7Yazorman@127.0.0.1>...
> Alvin Sebastian (asebastian@cmri.usyd.edu.au) writes:
> > I want to create a stored procedure which will extract the "GroupName"
> > from the record returned by "sp_helpuser". In order to do this I need
> > to execute "sp_helpuser" which returns the entire record. I want to
> > just extract the "GroupName" from the record and return it to my
> > application. How do I go about this?
>
> Either you access sysusers directly, you can use the INSERT EXEC construct:
>
> INSERT #temp (...)
> EXEC sp_helpuser
>
> You need to create #temp so that it agrees with the output from sp_helpuser.

Alvin Sebastian (asebastian@cmri.usyd.edu.au) writes:
> By the way, I forgot to mention in the first post that I'm only
> interested in the "GroupName" of the currently logged-on user so the
> stored procedure will be returning a single string value only and not
> a table. How should the stored procedure return this single value from
> the record returned by "sp_helpuser"?

A one-row result set is still a table.

There is the OPENQUERY method as well.

See http://www.algonet.se/~sommar/share_data.html where I discuss both
methods.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks Erland, I got it working now!