USER DEFINED FUNCTIONS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

USER DEFINED FUNCTIONS

Author

Topic

innocent73
Starting Member

20 Posts

Posted - 2002-05-07 : 06:34:16

Hello,
I have migrated an access predefined query to MS SQL Server 2000.But I want to retrieve this UDF like a table via ODBC.
Views in SQL Server do not support parameters vales..So..what shoul I do..?You can see this query(Named KalipBilgisi) below:
PLEASE HELP
*******************************************************
PARAMETERS [ModelKoduParam] Long, [KalipKoduParam] Long, [BedenKoduParam] Long;
SELECT T_MODEL.M_Adi, T_KALIP.K_Adi, T_BEDEN.BedenAdi
FROM T_MODEL, T_KALIP, T_BEDEN
WHERE (((T_MODEL.ModelKodu)=[ModelKoduParam]) AND ((T_KALIP.KalipKodu)=[KalipKoduParam]) AND ((T_BEDEN.BedenKodu)=[BedenKoduParam]) AND ((T_BEDEN.ModelKodu)=[ModelKoduParam]));
*************************************************************

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-05-07 : 06:49:55

Have you tried creating a pass-through query in Access? You can use this to execute any SQL code on your server, including stored procedure calls, etc. Not sure if it will do what you want, but give it a go.

Tim

KHeon
Posting Yak Master

135 Posts

Posted - 2002-05-07 : 07:08:17

Look up "user-defined functions" in SQLBOL. In SQL2000 you have the ability to create functions as well as the new table datatype. UDF's can return a "table".

For example (from SQLBOL):


CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S
             INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
        WHERE O.Freight > @FreightParm
   RETURN
END

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

innocent73
Starting Member

20 Posts

Posted - 2002-05-07 : 08:08:49

I would like to access to this UDF like a table or view via ODBC.Because a visual c++ recordset will access to this query.
My project was accessing to this predefined MS access query.And when I try to communicate with this access database then I could easily see this access query.
The problem is if I want to access this query via ODBC then I must create a VIEW...But views do not support parameter values..

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-05-07 : 08:13:50

none the less... when you say you need a view you really don't ... i assume you are using a SELECT * FROM MyView from your program ... if you lookup UDF's in BOL you can write the above query and do a SELECT * FROM dbo.MyUDF(Param1, Param2, Param3) ...

innocent73
Starting Member

20 Posts

Posted - 2002-05-07 : 08:23:33

so..please help me..I want to create a view named "KalipBilgisi".and predefined query in access database is as shown below:

PARAMETERS [ModelKoduParam] Long, [KalipKoduParam] Long, [BedenKoduParam] Long;

SELECT T_MODEL.M_Adi, T_KALIP.K_Adi, T_BEDEN.BedenAdi
FROM T_MODEL, T_KALIP, T_BEDEN
WHERE (((T_MODEL.ModelKodu)=[ModelKoduParam]) AND ((T_KALIP.KalipKodu)=[KalipKoduParam]) AND ((T_BEDEN.BedenKodu)=[BedenKoduParam]) AND ((T_BEDEN.ModelKodu)=[ModelKoduParam]));

Please help me..How can I create this view?????

innocent73
Starting Member

20 Posts

Posted - 2002-05-07 : 08:41:14

I have created this UDF..named KalipBilgisi......How can I access to this UDF via ODBC like a database table(like in MS access )

CREATE FUNCTION KalipBilgisi
(
@ModelKoduParam int,
@KalipKoduParam int,
@BedenKoduParam int
)
RETURNS table
AS
RETURN
(
SELECT T_MODEL.M_Adi, T_KALIP.K_Adi, T_BEDEN.BedenAdi
FROM T_MODEL, T_KALIP, T_BEDEN
WHERE
T_MODEL.ModelKodu=@ModelKoduParam
AND T_KALIP.KalipKodu=@KalipKoduParam
AND T_BEDEN.BedenKodu=@BedenKoduParam
AND T_BEDEN.ModelKodu=@ModelKoduParam
)

innocent73
Starting Member

20 Posts

Posted - 2002-05-08 : 02:40:21

"KalipBilgisi" predefined query can be seen via ODBC and DAO when I used an MS access database...How can I see this predefined query if I use an MS SQL Server.??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources