Passing Multi-Select ListBox Values to SQL Stored Proc

[Auguy]

What is the best method of passing the selected items from a multi-select list box to a SQL stored procedure so that all of the items are returned in the data set? In the past I have built dynamic SQL with an Inlist or a series of or's, but I am trying to emliminate the dynamic SQL.

Auguy
Northwest Ohio

 

[SqueakinSweep]

Good idea to eliminate the dynamic SQL. I've been through the same iteration process myself. The way I do it is this.

Pass1...create a backend SQL table eg UserFilter, and add in all the ids you require, and commit the SQL transaction. Good idea to also add a user identifier if you have one, so you can use this table for all users

Pass2..simply write your select statement so it uses an IN clause eg WHERE id in (select my_id from UserFilter where user_id=xx)

Good luck



Sweep
...if it works, you know the rest..
Always remember that Google is your friend

 

[Auguy]

Thanks Sweep, I will look into it.

Auguy
Northwest Ohio

 

[RiverGuy]

Expanding on Sweep's second point...I would suggest googling "SQL Server Split Function." There's lots of examples out examples out there, some better than others. But the idea is to pass a delimited string into a stored procedure which represents the values in your IN clause. The function parses the string and returns a table variable containing multiple records which you can use for a JOIN filter or an IN filter.

So, you pass in a string like the following:
"Item1;Item2;Item3;Item4"

and you end up with four records to use in your stored procedure:
Item1
Item2
Item3
Item4

 

[Auguy]

Thanks RiverGuy. I do remember reading something like that in the past few months. I better re-check by favorites to see if I saved them. That sounds like it might be the way to go

Auguy
Northwest Ohio