User Defined Sql Function and ADO.Net

Hi all,

Does anyone know the syntax for returning a value from a user defined
function in Sql Server using VB.Net.

Each time I do it I get:

invalid object name

We have a function which takes one parameter and returns a table.

I can find little or no documents directly addressing this subject.


Thanks in advance,

Bill

William
Thu Dec 04 13:11:19 CST 2003

Are you qualifying that function name with the owner? dbo.DateFunction_DT
instead of DateFunction_DT That can be a problem.

HTH,

Bill

"William Oliveri" <wuji@bigvalley.net> wrote in message
news:%231HrShpuDHA.2360@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> Does anyone know the syntax for returning a value from a user defined
> function in Sql Server using VB.Net.
>
> Each time I do it I get:
>
> invalid object name
>
> We have a function which takes one parameter and returns a table.
>
> I can find little or no documents directly addressing this subject.
>
>
> Thanks in advance,
>
> Bill
>
>

David
Thu Dec 04 14:02:56 CST 2003


"William Oliveri" <wuji@bigvalley.net> wrote in message
news:%231HrShpuDHA.2360@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> Does anyone know the syntax for returning a value from a user defined
> function in Sql Server using VB.Net.
>
> Each time I do it I get:
>
> invalid object name
>
> We have a function which takes one parameter and returns a table.
>
> I can find little or no documents directly addressing this subject.
>

This can be done. But here's a general rule: When in doubt, write your own
batch.

Run it it Query Analyzer, something like

declare @input1 varchar(50)
declare @rv int
set @rv = dbo.MyFunc(@input1)

Then use this in a command (CommandType.Text)

Then remove the declare's and attach SqlParameters to @rv
(ParameterDirection.Output) and @input1 (ParameterDirection.Input).

This works for stored procedures, functions, and arbitrary SQL.


Then if you want to get it running with CommandType.StoredProcedure, you at
least have someplace to start from.

David

William
Thu Dec 04 14:08:50 CST 2003

Thanks for the response. I just found an article which helped a bunch.

Here it is for others looking for the same thing:

http://www.ftponline.com/vsm/2003_09_14th/online/hottips/rehak/default_pf.aspx


Bill




"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:%23bFo0npuDHA.2464@TK2MSFTNGP12.phx.gbl...
> Are you qualifying that function name with the owner? dbo.DateFunction_DT
> instead of DateFunction_DT That can be a problem.
>
> HTH,
>
> Bill
>
> "William Oliveri" <wuji@bigvalley.net> wrote in message
> news:%231HrShpuDHA.2360@TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > Does anyone know the syntax for returning a value from a user defined
> > function in Sql Server using VB.Net.
> >
> > Each time I do it I get:
> >
> > invalid object name
> >
> > We have a function which takes one parameter and returns a table.
> >
> > I can find little or no documents directly addressing this subject.
> >
> >
> > Thanks in advance,
> >
> > Bill
> >
> >
>
>