How to pass an array of integers as sql parameter
-
Hi,
I have a list of all the userids which I want to pass as a parameter to a stored procedure from my webpage. Can any one tell me which is the best way to do this? The list size is big, around 25,000 userids.
I am using C#
Thanks.
-
25,000 items
That's some list. Even if each of these 25,000 values is only one digit, you'll still exceed the limits of the varchar datatype so you'll need to use text. That comes with its own problems and caveats.
Maybe you should look at an alternative method of getting these values into a database table, like saving them to a text file first or something like that.
--------------------
Colt 45 - the original point and click interface
-
Actually I tried sending the list as a text variable parameter, a comma seperated text and convert each userid back to int. I have a table variable inside the stored proc and I convert each userid to int and store it in the table. It worked couple of times. But now it is throwing an error, Syntax error converting the varchar value '6633,156,150,1559' to a column of data type int.
I checked sql profiler. It looks like it times out inside the loop, where I get userid one by one. After looping through n times, it stops at one point - sql:stmt starting @intid = convert(int, @stringid). Then rpc:starting exec sp_reset_connection. It stops here.
-
You might want to read thoroughfully through the article PW referenced. It should give you some ideas.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The article on Dynamic SQL linked by PW is very comprehensive.
How did the 25,000 user IDs get to the webpage in the first place? Did they come from the database? 25,000 is too many for input or selection by a user....
These 25,000 items should be in a table in the DB, maybe with a "SELECTED" boolean field if there are more that aren't used, make your query join onto that.
Or am I way off the mark here? Passing 25,000 of anything to (or from) a web page just does not sound like a good idea
-
Yea, I'd agree with all of the above. My thoughts are to create a table / temp table that you can populate with the 25,000 ids, then use a subquery in the SP to reference them. It may be possible to create a table variable type too. You may want to experiment with the different methods to see what's faster.
Cheers.
-
The 25000 entries come from a webpage. I have a search clients page, where users can search for and select the clients. And there are mroe than 25000 users in the database, so in the worst case all of them can be selected. I can store it in some table but what if more than one user run the function simultaneously.
-
You have a UI that displays all 25,000 clients to the user in some sort of user-selectable grid or list ? How can "all" be selected ?
I think maybe the problem is not getting 25,000 ID's back to the DB, but in the design of a UI that presents 25,000 selectable items to a user ...
-
The search results are presented in a grid. User can click on 'select all' or 'select none'. I am not sure there is another way of designing the UI. It is a search page and if the user does not enter any search criteria or enters something that eliminates only few users, I have to display around 25000 results.
-
25000 results is practically unusable to any user. Maybe you should place a limit on the rows returned, which, if exceeded gives a warning of "There were more rows returned than you can possibly consider viewing" or similar, so that the result is the user re-running the query with more restrictive search filters.
This in turn will produce a more managable set of ID's going into your stored procedure.
-
But this search page is for admin, not for public users. They might want to view all the users or export all the users into a csv list for some admin work. So I cannot put a limit on that.
-
A good design pattern would be to transfer the selected items between the web page and the web server as XML data. Then you can design the stored procedure with a single parameter that takes the XML string. Then you use OPENXML inside the procedure to get to you data and use it in subsequent queries.
HABIB.
-
WRT limiting the select options:
You could always consider "all" as a special option - pass a flag instead of passing the actual user ids. Should be a bit faster, since you don't have to parse out 25,000 id values. Then, if they don't want "all", they're limited to 500 or 1,000 records. large enough that they can grab a bunch at once if they need to, but not so large that things get out of hand.
This may avoid the time-out issue with your old methodology.
Of course, the question "Is it possible to rework the UI at this point" arises....
R David Francis
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply