VB Functions or SQL Server Stored Procedures??

[cwarner]

Hi,

I wanted to know, if I write a VB Public Function,
say "Public Function GETNUMBERS(street as string) as string", the function will get the numbers from a street address. I built this function already, but what I want to know is - can I APPLY this function within a SELECT statement in VB, applying it directly to a SQL Server table? I do this in Access and apply the function diretly to access tables, e.g.:

"SELECT getnumbers(a.street) as streetnums FROM tbl_vendors"

Or -- do I have to go the more laborious route of making all of my VB functions into SQL Server STored Procedures?

Thanks!
Christy.

 

[jeepxo]

try this

dim rsStreet as new ADODB.Recordset
dim oConn as new ADODB.Connection
dim streetNums() as variant
dim i as integer

oConn.open "database","username", "password"
rsStreet.Open "Select street from tbl_vendors", oConn, adopendynamic, adlockpessimistic
i=1
while not rsStreet.EOF then
redim Preserve streetNums(i)
streetNums(i) = getNumbers(rsStreet("street").value)
i = i+1
rsStreet.MoveNext()
Wend


"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[mattKnight]

Or -- do I have to go the more laborious route of making all of my VB functions into SQL Server STored Procedures?

The performance boost of using Stored Procedures may well be worth considering.

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[fredericofonseca]

You can have that function defined as a UDF within SQL Server and still not use SP´s.

But performance wise it´s better to have your SQL as stored procedures, and associate them with your functions (UDF´s)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cwarner]

all,

thanks for the responses... yes I was trying to get away from the recordset method because it's more time costly. I guess stored procedures is the way to go because SQL server will then have to do the # crunching.

Thanks,
Christy.