How to make multiple-valued in one parameter in Query Builder?

Dear all,

I have a problem over here, hope to get some good advices from you guys.

I use Query Builder in VS2005 where you could see some tables on top, queries on the middle and resultset on the bottom windows.

I have a statement like below:

SELECT...WHERE Booking.BookingType IN (@.Type1, @.Type2, @.Type3, @.Type4, @.Type5)...

However, I found out that there are too many parameters, I wish to have only ONE parameters in the list of IN() operator. Is there any way to do it? Thanks!

I would suggest giving a look to Jens Suessmeyer's SPLIT function; the definition of this function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

You ought to be able to code up your lists as comma separated strings and create a query that

looks something like this:

Code Snippet

SELECT...

from Booking

join split (@.yourParm, ',')

on Booking.BookingType = splitValue

Another alternative is to pass your argument as XML and use SQL Servers XML ability to interpret the XML argument.

Another question: Are you trying to make a parameter similar to a multi-value parameter that is available in Reporting Services? If the answer to that question is "YES" then you need to understand that feature is only available in Reporting Services and is not in general available to Transact SQL.

|||

If you are trying to reduce the number of parameters, just remove them from inside the paretheses.

In fact, if you are ONLY testing for a single parameter, use equals instead of IN (...) -it may be a bit more efficient.

|||

Actually, what I wish to have is putting a list of values in the @.Param programmatically in my code, thus, I wish to have one single @.Param instead of multiple @.Param(s) in SQL. I used Query Builder GUI to build my SQL query in VS2005, however, I couldn't find way to just put one @.Param and set multiple values in it.

For e.g.

SELECT * FROM Booking WHERE Type IN ( @.Param );

C# Code:

....DataTable1.GetData("Lab, Meeting, Course");

Is this possible to do this?

Thanks!

|||

There is no direct way to do something like

Code Snippet

SELECT * FROM Booking WHERE Type IN ( @.Param );

in which @.param contains data such as 'Lab, Meeting, Course'. Again, I would suggest using the SPLIT function to accomplish your objective.