comma seperated values

Jeremy Collins

Delphi Developer

Re: Stored Procedure passing Array / comma seperated values

2004-05-20 08:30:54 PM
delphi181
Aji writes:

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

Dennis

Delphi Developer

2004-05-20 08:36:59 PM

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.

alex

Delphi Developer

2004-05-20 09:58:26 PM

Re: Stored Procedure passing Array / comma seperated values

Maybe it will be more efficient to populate temp table ?
<Dennis Passmore>writes

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.

Aji

Delphi Developer

2004-05-20 10:00:49 PM

Re: 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 delphi.newswhat.com

Del Murray

Delphi Developer

2004-05-20 10:38:02 PM

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

Aji

Delphi Developer

2004-05-20 10:39:02 PM

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 delphi.newswhat.com

Aji

Delphi Developer

2004-05-21 03:58:47 PM

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 delphi.newswhat.com