Rewrite a stored procedure to UDF

I want to rewrite a stored procedure to user defined function because I
want to insert into a table a subset of the returned columns from stored
procedure.
I want to insert UserName, GroupName and LoginName from sp_helpuser into
a table.
Prease help me how to rewrite this procedure to function.
I test the function like this but is not a correct sintax of function:
create function fn_helpuser (@.name_in_db sysname=null)
returns table
as
begin
return (exec sp_helpuser @.name_in_db)
end
Thanks for help!It's going to be quite a bit more work than that... First check out the
source for it. Put Query Analyzer into Results-in-text mode and do:
use master
GO
EXEC sp_helptext 'sp_helpuser'
GO
--
You'll have to tear that apart and take whatever you need. Very doable, but
not as trivial as just returning it from a function, unfortunately.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:EC7AA1B5-9CDE-4A71-A3E4-26CF67BEA942@.microsoft.com...
> I want to rewrite a stored procedure to user defined function because I
> want to insert into a table a subset of the returned columns from stored
> procedure.
> I want to insert UserName, GroupName and LoginName from sp_helpuser
into
> a table.
> Prease help me how to rewrite this procedure to function.
> I test the function like this but is not a correct sintax of function:
> create function fn_helpuser (@.name_in_db sysname=null)
> returns table
> as
> begin
> return (exec sp_helpuser @.name_in_db)
> end
> Thanks for help!
>|||Mihaly,
What about creating a table to grab the result of the sp.
Example:
create table #t (
UserName sysname null,
GroupName sysname null,
LoginName sysname null,
DefDBName sysname null,
UserID smallint null,
sid varbinary(85) null
)
insert into #t
execute sp_helpuser
select UserName, GroupName, LoginName from #t
drop table #t
go
AMB
"Mihaly" wrote:

> I want to rewrite a stored procedure to user defined function because I
> want to insert into a table a subset of the returned columns from stored
> procedure.
> I want to insert UserName, GroupName and LoginName from sp_helpuser int
o
> a table.
> Prease help me how to rewrite this procedure to function.
> I test the function like this but is not a correct sintax of function:
> create function fn_helpuser (@.name_in_db sysname=null)
> returns table
> as
> begin
> return (exec sp_helpuser @.name_in_db)
> end
> Thanks for help!
>