Creating custom functions for SQL Server

[David C. Holley]

Is there a really good reference/tutorial on creating functions for SQL
Server? One of the questions that I have is wether or not it is possible
to create VBScript/VBA functions for SQL Server. I know that it is
possible to create user defined T-SQL functions, however it appears that
T-SQL is too limited for what I need to do.

[Aaron [SQL Server MVP]]

> to create VBScript/VBA functions for SQL Server. I know that it is

> possible to create user defined T-SQL functions, however it appears that
> T-SQL is too limited for what I need to do.

Bwahahaha! That's funny. Maybe you could tell us what you need to do
before assuming that T-SQL is too limited to do it.

And no, BTW, you can't create SQL Server functions using VB/VBA. You can
create user-defined functions using T-SQL, or you can created extended
stored procedures using C++. You will be able to create procedures, UDTs,
aggregates, etc. using VB.Net or C# in Yukon ... but note that they will not
necessarily be doing things that T-SQL isn't capable of; more or less they
will be adding techniques to do the same things in different ways (in some
cases faster or with less code than T-SQL).

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Bob Lehmann]

Kinda like my truck is limited in that I can't use it as a boat?

Stoopid truck! Stoopid Nissan!

Bob Lehmann

"David C. Holley" <DavidCHolley@netscape.net> wrote in message
news:u3j6BBIYEHA.3716@TK2MSFTNGP10.phx.gbl...

> Is there a really good reference/tutorial on creating functions for SQL
> Server? One of the questions that I have is wether or not it is possible
> to create VBScript/VBA functions for SQL Server. I know that it is
> possible to create user defined T-SQL functions, however it appears that
> T-SQL is too limited for what I need to do.
>

[David Holley]

Actually, in writing my initial response, I can distill the problem down
to this...

There are two tables - tblZones and tblRates. As records are added to
tblZones a trigger will fire that will automatically insert records into
tblFares. How would I create a WHERE statement to prevent duplicate
records from being added into tblFares?

The particular schema is neccessary because of a third table
(tblLocations) that contains the records for the various specific
locations. Basically you grab a location as the Origination, then grab
another as the Destination and then look up the rate based on the zones
that the ORG & DEST are in. Because of this tblRates needs to have two
records for each combination of zones as in ...

ORG DEST FARE
1 1 $25
1 2 $45
1 3 $35
2 1 $25
3 1 $35

Another twist on this is that tblRates will have a third column
indicating product. So if there are two products the records would
appear as

PRODUCT ORG DEST FARE
LTC 1 2 $25
LTC 1 3 $45
LTC 2 1 $25
LTC 3 1 $45
SLM 1 2 $25
SLM 1 3 $45
SLM 2 1 $25
SLM 3 1 $45

Hopefully, that explains everything.

David

tblLocations has a one to many relationship with tblRates

A physical rep of the relationship between tblLocations & tblRates would
be
tblLocation -on Zone No-> tblRates <-on Zone No-tblLocation

[url]www.gatewayorlando.com[/url]

*** Sent via Devdex [url]http://www.devdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[David Holley]

Actually, about an hour after I wrote that last response, the solution
presented itself - abeit its a SQL statement that will probably be about
a furlong in length. Haven't created it yet, but at least I know what
direction to go. Will post back.

David H

[url]www.gatewayorlando.com[/url]

*** Sent via Devdex [url]http://www.devdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[David C. Holley]

I have created FIRE!!

David H
(The trigger is alive and working nicely.)

David Holley wrote:

> Actually, about an hour after I wrote that last response, the solution
> presented itself - abeit its a SQL statement that will probably be about
> a furlong in length. Haven't created it yet, but at least I know what
> direction to go. Will post back.
>
> David H
>
> [url]www.gatewayorlando.com[/url]
>
> *** Sent via Devdex [url]http://www.devdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!