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,
--
JMorrellWhy? 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...
> > 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
>
>|||(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
> 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...
> > > 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
> >
> >
> >