Executing SQL FUNCTION

[Luis Moises Rojas]

If i wrote my function on a SQL SERVER (on User Defined Functions on the Database). How can i call from My VB project and how could i pass parameters?

[Briantcva]

function as stored procedure?

[Luis Moises Rojas]

A Function.

[Luis Moises Rojas]

Sorry. Not store procedure as function. A user defined function, saved on this folder on the database.

[techgnome]

I don't think ADO can call functions directly... (but I could be wrong)... but you could use a stored procedure as a "wrapper" to calling the function.
So, it would work like this app (using ADO) calls the stored procedure, which calls the function.... parameters would be passed to the stored proc which would pass them to the function, and the result would then get passed back through an output parameter...

[Briantcva]

I've never heard of such a thing. Which doesn't really mean anything. Good luck.

[techgnome]

Originally posted by Briantcva
I've never heard of such a thing. Which doesn't really mean anything. Good luck.

Starting w/ SQL2000, you can create Functions which can return a sincgle value (scalar) or a table (table function).... these are neat in that they can be used as part of a larger SQL statement, the way you can include a view, but they are better than a view because you can pass parameters to them, like a stored proc.
Normally you can't join to a stored proc or use the return value of a stored proc as a field in a select, so MS created UDFs, USer Defined Functions..... we've found then quite handy and wonderfull.

[Briantcva]

And now I feel better and not having heard of them (running NT and SQL 7). Very cool. Thanks.

[Luis Moises Rojas]

sqlstring = "Select top 1 suscripcion.dbo.ultimo_dia_del_mes(" & CVar(fecha) & ") from parametros "
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open sqlstring, cn, adOpenForwardOnly, adLockReadOnly
x = rs.Fields(0)

Where
suscripcion.dbo=Database
ultimo_dia_del_mes=Function