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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-12 : 08:36:20
|
Michael writes "I am using SQL Server 7.0 on Windows 2000 SP1 for a project, and I can't figure out how to pass a large array of values to a stored procedure. Perhaps some background will help.
My program is querying a non-SQL data source for a list of paths and filenames. This list could potentially be very long. For example, the list might look like this:
/path1/filename1.ext
/path1/filename2.ext
/path2/filename1.ext
/path2/path1/filename1.ext
etc...
I would then like to pass this list to a SQL 7.0 stored procedure for use with an IN clause. For example:
SELECT *
FROM Table
WHERE ObjectPath IN ('/path1/filename1.ext',
'/path1/filename2.ext',
'/path2/filename1.ext',
'/path2/path1/filename1.ext')
I tried to create a varchar parameter, but I quickly ran into its upper size limit.
I considered passing XML, but I think that I would again run into a size limitation, and I'm not sure how well this would work with SQL 7.0 (I know that SQL 2000 improves this area quite a bit, but I can't use that version on this project).
I also tried passing to a text parameter, but I couldn't use that parameter with my IN clause, because SQL Server won't allow me to use a text parameter that way.
I also considered creating a temporary table and querying against that, but due to the potential size of the array and the number of hits against this web application, I concluded that I couldn't suffer that kind of performance hit.
So, in the end, I'm building up a dynamic SQL statment and passing it along. This might not be the best way, but it is the only way I could figure out that would be somewhat performant.
Am I missing something? Is there a better way to accomplish this? My requirements are:
1) Must be able to pass a very large array of string values
2) Must be a performant solution under heavy load
Any help you can provide will be *greatly* appreciated.
Thanks,
Mike" |
|
|
|
|
|
|
|
Passing a Large Array of Values to a Stored Procedure