BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Stored Procedure passing Array / comma seperated values          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Jeremy Collins Guest Posted: Thu May 20, 2004 12:30 pm    Post subject: Re: Stored Procedure passing Array / comma seperated values Aji wrote: Quote: I have one question. I am using SQLServer 2000 and Delphi 7 - dbexpress. My requirement is to pass an array of IDs to a stored procedure. Since we cant use array as parameter, I used comma seperated values. This works ok, if the input to stored procedure is within a particular limit. But when the list of values increases, it seems the input value gets truncated when it reaches the stored proc. How do you build the list of IDs in the first place? Is it possible to construct a query to build the list? -- jc Remove the -not from email Back to top Dennis Passmore Guest Posted: Thu May 20, 2004 12:36 pm    Post subject: Re: Stored Procedure passing Array / comma seperated values Use a secondary work table and pre-populate it before calling the stored procedure and then in the stored procedure JOIN to it just like any other table. It will be more maintainable and run faster since SQL server does not like IN lists in select statements. Dennis Passmore Ultimate Software, Inc. Back to top alex Guest Posted: Thu May 20, 2004 1:58 pm    Post subject: Re: Stored Procedure passing Array / comma seperated values Maybe it will be more efficient to populate temp table ? <Dennis Passmore> wrote Quote: Use a secondary work table and pre-populate it before calling the stored procedure and then in the stored procedure JOIN to it just like any other table. It will be more maintainable and run faster since SQL server does not like IN lists in select statements. Dennis Passmore Ultimate Software, Inc. Back to top Aji Guest Posted: Thu May 20, 2004 2:00 pm    Post subject: Stored Procedure passing Array / comma seperated values Hi all, I have one question. I am using SQLServer 2000 and Delphi 7 - dbexpress. My requirement is to pass an array of IDs to a stored procedure. Since we cant use array as parameter, I used comma seperated values. This works ok, if the input to stored procedure is within a particular limit. But when the list of values increases, it seems the input value gets truncated when it reaches the stored proc. The list of comma seperated values is defined as varchar and has size of 8000. Any suggestions on how we can solve this prob ? Another related ques, Can I use XML to pass the values to stored proc ? If so, how can I use this from Delphi ? Thanks & Best Regards Ajith Ajith --- posted by geoForum on http://delphi.newswhat.com Back to top Del Murray Guest Posted: Thu May 20, 2004 2:38 pm    Post subject: Re: Stored Procedure passing Array / comma seperated values Yes , much more efficient. Once you use that "in" list, then sql server hasn't much chance of optimizing the query Back to top Aji Guest Posted: Thu May 20, 2004 2:39 pm    Post subject: Re: Stored Procedure passing Array / comma seperated values Quote: How do you build the list of IDs in the first place? Is it possible to construct a query to build the list? I have some objects in a list, which have property ID. From this, I make comma seperated IDs(just string concatenation). Nop, it is not possible to use a query to build the list. Ajith --- posted by geoForum on http://delphi.newswhat.com Back to top Aji Guest Posted: Fri May 21, 2004 7:58 am    Post subject: Re: Stored Procedure passing Array / comma seperated values Quote: Yes , much more efficient. Once you use that "in" list, then sql server hasn't much chance of optimizing the query Thanks for ur suggestions. In the method suggested, I have to populate the IDs one by one to the temp table and then use the same in stored proc. Once my usage is finished, I can delete the temp table. Did I get it right ? In oracle we had something like Array DML, were we could send many records in a go, which speeds up the process. Does SQL server has something similar ? Another question is regarding the usage of XML. Can we write the data in an xml format, pass the same to SQL Server. Use OpenXML to access the data from XML doc. Here also do I have to pass the xml value as string ? Ajith --- posted by geoForum on http://delphi.newswhat.com Back to top Ray Carpenter Guest Posted: Thu Jul 29, 2004 5:44 pm    Post subject: Re: Stored Procedure passing Array / comma separated values When using a temporary work table in MS SQL Server you will need to give it a connection-specific name if there is any possibility that other connections are doing the same thing at the same time. You could, for example, append @@SPID to the fixed name of the table to obtain a connection-specific name. Regards Ray Carpenter <Dennis Passmore> wrote Quote: Use a secondary work table and pre-populate it before calling the stored procedure and then in the stored procedure JOIN to it just like any other table. It will be more maintainable and run faster since SQL server does not like IN lists in select statements. Dennis Passmore Ultimate Software, Inc. 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.