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!
>

returning unique data

I have a table with 3 columns: number, name, rowid (and identity column). I
want to return the name of the first row (first meaning lowest rowid) when
there are more than one row with the same number. Given the table looks
like this:
number name rowid
102 bob 1
102 allen 2
104 carl 3
104 mike 4
I want only bob and carl to be returned.
Can anyone point me in the right direction?
Tx for any help.
Bernie YaegerYou're looking for a correlated subquery.
CREATE TABLE #foo
(
rowid INT IDENTITY(1,1),
[name] VARCHAR(12),
[number] INT
)
SET NOCOUNT ON
INSERT #foo([name], [number]) SELECT 'bob', 102
INSERT #foo([name], [number]) SELECT 'allen', 102
INSERT #foo([name], [number]) SELECT 'carl', 104
INSERT #foo([name], [number]) SELECT 'mike', 104
SELECT [name],[number],rowid
FROM #foo f1
WHERE rowid = (SELECT MIN(rowid)
FROM #foo f2
WHERE f2.[number] = f1.[number])
ORDER BY [number]
DROP TABLE #foo
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Bernie
CREATE TABLE #Test
(
[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
number INT NOT NULL,
[name]CHAR(1)NOT NULL,
rowid INT NOT NULL
)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
SELECT [name]FROM #Test
WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Hi Uri,
Tx for your help Uri.
Bernie
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLVoxKmkFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Bernie
> CREATE TABLE #Test
> (
> [ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> number INT NOT NULL,
> [name]CHAR(1)NOT NULL,
> rowid INT NOT NULL
> )
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
> INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
> INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
>
> SELECT [name]FROM #Test
> WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>|||Hi Aaron,
Tx so much for your help.
Bernie
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ORACDEmkFHA.708@.TK2MSFTNGP10.phx.gbl...
> You're looking for a correlated subquery.
>
> CREATE TABLE #foo
> (
> rowid INT IDENTITY(1,1),
> [name] VARCHAR(12),
> [number] INT
> )
> SET NOCOUNT ON
> INSERT #foo([name], [number]) SELECT 'bob', 102
> INSERT #foo([name], [number]) SELECT 'allen', 102
> INSERT #foo([name], [number]) SELECT 'carl', 104
> INSERT #foo([name], [number]) SELECT 'mike', 104
> SELECT [name],[number],rowid
> FROM #foo f1
> WHERE rowid = (SELECT MIN(rowid)
> FROM #foo f2
> WHERE f2.[number] = f1.[number])
> ORDER BY [number]
> DROP TABLE #foo
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>