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 |
|
jdattis
Starting Member
14 Posts |
 Posted - 2005-01-25 : 15:33:43
|
| All,I have a need to pass in multiple string variables into a sp, in the form of:s1 = '1,2,3's2 = '4,5,5,7'Something like that. I want my query to look like this:SELECT *FROM tblWHERE Field1 IN (@s1) AND Field2 IN (@s2)Field1 and Field2 are int's. So, it can't convert. Does anyone have a "better" solution than creating a string and running an exec. That kinda defeats the purpose of the sp in my opinion.Thanks,Jake |
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2005-01-25 : 15:35:33
|
| You can create a table function that accepts the string of comma separated values and returns a table with one column and a row for each value. Then you can join with the table function in your select statement. |
 |
|
|
jdattis
Starting Member
14 Posts |
Posted - 2005-01-25 : 15:40:09
|
quote:
Originally posted by BlackDog
You can create a table function that accepts the string of comma separated values and returns a table with one column and a row for each value. Then you can join with the table function in your select statement.
Hate to sound dumb, but I have never done that before. Do you have the time to type out a simple example?Thanks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jdattis
Starting Member
14 Posts |
Posted - 2005-01-25 : 16:16:02
|
| What would be the best way to handle an empty string situation. For example, say the source web page had checkboxes, and none were checked. |
|
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2005-01-25 : 17:27:42
|
| Sorry for the lack of detail in my posting, I was in a hurry. The link brett posted shows a good example.Using the table function, it will handle the empty string situation when you join to the table function. If you have 10 checkboxes and they don't select any, then when you pass the string into the table function you will end up with a table that has either no rows or 10 rows with values of NULL (depending on how you want to handle it.) So when you join tbl to the table function, no rows will match on the join and no data will be returned.HTHRyan |
|
|
|
|
|
|
|
|
dynamic in clause