BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Is this possible ?!          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Florian Teichert Guest Posted: Mon Jul 05, 2004 2:11 pm    Post subject: Is this possible ?! Hi everybody, it would be of much help to me to manage something like the following: Assume existing: - table X={PK, SomeData} - userdef. function MyFunc(take X.PK) returning table={a, b, c} -> returning just one row to extend X's The task now is to concat table X using MyFunc's output This doesn't work (table is not array of values) - but should give you a clue of what I'm trying to do: SELECT *, MyFunc(PK) FROM X Thanks you in advance Florian btw: I'm using MS-SQL Server 2000 Back to top Robert Cerny Guest Posted: Mon Jul 05, 2004 6:55 pm    Post subject: Re: Is this possible ?! AFAIK, a function can return scalar (single value), not result set. On some servers (IB, ASA,...) you can use result set from stored proc in a select query. -- Robert Cerny DelphiShaman "Florian Teichert" <floteich (AT) hrzpub (DOT) tu-darmstadt.de> wrote Quote: Hi everybody, it would be of much help to me to manage something like the following: Assume existing: - table X={PK, SomeData} - userdef. function MyFunc(take X.PK) returning table={a, b, c} - returning just one row to extend X's The task now is to concat table X using MyFunc's output This doesn't work (table is not array of values) - but should give you a clue of what I'm trying to do: SELECT *, MyFunc(PK) FROM X Thanks you in advance Florian btw: I'm using MS-SQL Server 2000 Back to top Del Murray Guest Posted: Tue Jul 06, 2004 2:30 am    Post subject: Re: Is this possible ?! SQL Server can return a table as a variable (weird huh?) from a UDF Back to top Florian Teichert Guest Posted: Tue Jul 06, 2004 5:42 am    Post subject: Re: Is this possible ?! Robert Cerny schrieb: Quote: AFAIK, a function can return scalar (single value), not result set. The one below is from MSSQL-Help - and at the time I use it like: select * from RealTable left join FuncResultingTable() ON RealTable.PK = FuncResultingTable.PK This is very slow because a full table scan of a really large table is used within FuncResultingTable() - even if only a small fraction is needed. Ideas? 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 Quote: On some servers (IB, ASA,...) you can use result set from stored proc in a select query. I'm afraid MSSQL will not ... Back to top Florian Teichert Guest Posted: Tue Jul 06, 2004 5:44 am    Post subject: Re: Is this possible ?! Del Murray schrieb: Quote: SQL Server can return a table as a variable (weird huh?) from a UDF .... indeed ... sounds like pushing some abstraction to the limits ... some day ... Back to top Del Murray Guest Posted: Tue Jul 06, 2004 5:05 pm    Post subject: Re: Is this possible ?! yes, and using those udf's to do that can be hard on sql server under the right conditions. I think there is a problem with him developing a good "plan" for the sql execution when the UDF is called by a Stored Procedure. He needs lots of hints to do a good job. It also makes it hard for feeble brained programmers like me to figure out what someone is doing, sometimes it seems like technology for technologies sake. Back to top Karl-Heinz Reng Guest Posted: Tue Jul 06, 2004 5:57 pm    Post subject: Re: Is this possible ?! Hello, this is in German, and yes "it is Mission possible" if you use SQL Server 2000 Sp3!! Neue Informationen - SQL Server 2000 SP3. Try this for Search: CREATE FUNCTION LargeOrderShippers ( @FreightParm money ) In the German Help you get two answers. Sometimes it is better to send all information of a problem to a newsgroup and not a part of it. If you use this without Sp3, you have a big problem! "Del Murray" <Del.Murray (AT) N_S_CreditHawk (DOT) Net> schrieb im Newsbeitrag news:40eadaee (AT) newsgroups (DOT) borland.com... Quote: yes, and using those udf's to do that can be hard on sql server under the right conditions. I think there is a problem with him developing a good "plan" for the sql execution when the UDF is called by a Stored Procedure. He needs lots of hints to do a good job. It also makes it hard for feeble brained programmers like me to figure out what someone is doing, sometimes it seems like technology for technologies sake. Back to top Florian Teichert Guest Posted: Wed Jul 07, 2004 6:55 am    Post subject: Re: Is this possible ?! Del Murray schrieb: Quote: yes, and using those udf's to do that can be hard on sql server under the right conditions. I think there is a problem with him developing a good "plan" for the sql execution when the UDF is called by a Stored Procedure. He needs lots of hints to do a good job. It also makes it hard for feeble brained programmers like me to figure out what someone is doing, sometimes it seems like technology for technologies sake. .... not for technologies sake but sometimes to make something work under quite bad conditions - most of my issues emerge from ~12 year old code/db-design - which is not my own product. Cause we need to take a lot of users with us we are redesigning our app in a step-by-step approach - with frequent problems to implement features on a (at least from a modern pov) cumbersome basis ... What I was trying to do here is extending a table with some (~15) calculated fields (keeping db-structure improving handling). What I did now is writing ~15 scalar UDFs and using a view to combine 'em with my 'physical'-table. This is very clumsy code in many details - question only was whether it is possible or not to put 15 scalar UDFs into 1 returning an array of 15 values and use it as above - which is not as it seems. Sorry, if I was unclear - nevertheless thanks for your inputs, Florian Back to top Robert Cerny Guest Posted: Wed Jul 07, 2004 3:37 pm    Post subject: Re: Is this possible ?! "Florian Teichert" <floteichNO (AT) SPAMhrzpub (DOT) NOBOTtu-darmstadtUURGH.de> wrote in message news:40EA3BD0.6090507 (AT) SPAMhrzpub (DOT) NOBOTtu-darmstadtUURGH.de... Quote: On some servers (IB, ASA,...) you can use result set from stored proc in a select query. I'm afraid MSSQL will not ... You just explained it can, just that MS calls it "function". Horrible performance is to a degree expectable, as result set from SP (or MS function) has ho keys, so if relation can't be optimized the other way (or server doesn't do it), join is sequential scan of both tables. Also, if mssql supports deterministic functions, declare it as deterministic. -- Robert Cerny DelphiShaman Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.