Convert User Defined Function

We have a User Function that gets called from Access. We now have a need to
use that same User Function in a VB application. Is there a way to convert
the User Function to a Stored Procedure?
I'm pretty green about this, so any help is appreciated.
tia,
JMorrell
Why? IIRC, you can call a function from VB (we do that occasionally from C#
and VB.Net).
Anyway, we can't help you convert the function to a procedure unless you
show us what the function does (!).
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> We have a User Function that gets called from Access. We now have a need
to
> use that same User Function in a VB application. Is there a way to
convert
> the User Function to a Stored Procedure?
> I'm pretty green about this, so any help is appreciated.
> tia,
> --
> JMorrell
|||Thanks for the reply. We're needing the results of the function within vb.
The function is as follows:
CREATE FUNCTION ufn_GetSubtree
(
@.supervisorid AS int
)
RETURNS @.tree table
(
employeeid varchar(6)NOT NULL,
supervisorid varchar(6)NULL,
lname varchar(25) NOT NULL,
fname varchar(25) not null,
term int not null,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT employeeid, supervisorid, lname, fname, term,
@.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE term = 0
and employeeid = @.supervisorid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
@.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @.tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
END
RETURN
END
I'm not sure how to go about using the function as is when in vb. Is there
a source for help in this?
tiaa,
JMorrell
"Aaron [SQL Server MVP]" wrote:

> Why? IIRC, you can call a function from VB (we do that occasionally from C#
> and VB.Net).
> Anyway, we can't help you convert the function to a procedure unless you
> show us what the function does (!).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
> news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> to
> convert
>
>
|||(a) I suggest CREATE FUNCTION dbo.ufn_GetSubTree
(b) this would be easy to convert to a stored procedure (though I'm sure
Celko will want to re-educate you on the right way to handle trees, nested
sets and heirarchies etc.).
(c) try this in VB:
dim conn as adodb.connection
conn.open "your connection string"
dim rs as adodb.recordset
rs.open "SELECT * FROM dbo.ufn_GetSubTree"
...
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:FECA6975-BE71-4CAB-8107-67598D6C921D@.microsoft.com...
> Thanks for the reply. We're needing the results of the function within
vb.
> The function is as follows:
> CREATE FUNCTION ufn_GetSubtree
> (
> @.supervisorid AS int
> )
> RETURNS @.tree table
> (
> employeeid varchar(6) NOT NULL,
> supervisorid varchar(6) NULL,
> lname varchar(25) NOT NULL,
> fname varchar(25) not null,
> term int not null,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT employeeid, supervisorid, lname, fname, term,
> @.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
> FROM tblemp
> WHERE term = 0
> and employeeid = @.supervisorid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
> @.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
> FROM tblEmp AS E JOIN @.tree AS T
> ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> --
> I'm not sure how to go about using the function as is when in vb. Is
there[vbcol=seagreen]
> a source for help in this?
> tiaa,
> JMorrell
> "Aaron [SQL Server MVP]" wrote:
from C#[vbcol=seagreen]
need[vbcol=seagreen]