Aug 23, 2011

Passing jQuery MultiSelect values to an SQL Server Parameter


The Problem

There are various jQuery plugins out there that allow you to convert a simple dropdown list into a multi-select one. They all return either an array of the selected choices, or a comma-separated string like 'opt1,opt3'. We generally take this value and put it into a hidden field we can access on the server side and try and pass it to our store procedure or query as a parameter. All goes well till you find that the query/procedure doesn't give you the expected results anymore if it has the keyword "IN", and you're trying to filter your results based on your dropdown's selection.
When you pass a string 'Miami,Berlin,London' as a parameter, say @Cities, to a query like

SELECT Population FROM Cities WHERE Name IN @Cities

It will only produce the correct results if you select only one checkbox from the multiselect. The reason is SQL server needs a list to come after the “IN” keyword, and 'Miami,Berlin,London' is not a list, but a single string literal. The query will try and look for a single record where the city name is 'Miami,Berlin,London' as it currently looks like this:

SELECT Population FROM Cities WHERE Name IN ('Miami,Berlin,London')

What we need to send the query is this:

SELECT Population FROM Cities WHERE Name IN ('Miami','Berlin','London')

The Solution

We can create a simple table valued function that takes our comma-separated string and returns a list that the “IN” keyword can work with. Here’s a sample:

CREATE FUNCTION [dbo].[SplitListWithDelimiter](
@InputString VARCHAR(8000) 
, @Delimiter VARCHAR(2) = ',' 
) RETURNS @List TABLE (Item VARCHAR(1000))
 
BEGIN
DECLARE @Item VARCHAR(1000)
WHILE CHARINDEX(@Delimiter ,@InputString ,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@InputString ,1,
CHARINDEX(@Delimiter ,@InputString ,0)-1))),
@InputString =RTRIM(LTRIM(SUBSTRING(@InputString ,
CHARINDEX(@Delimiter,@InputString ,0)+
LEN(@Delimiter ),LEN(@InputString ))))
IF LEN(@Item) > 0
INSERT INTO @List SELECT @Item
END
 
IF LEN(@InputString ) > 0
INSERT INTO @List SELECT @InputString 
RETURN
END

Now in our procedure or query, all we need to do is call this function and pass it our string:

SELECT Population FROM Cities WHERE Name IN
(SELECT Item FROM dbo.SplitListWithDelimiter(@Cities,','))

This will give us a result like this:
Item____
'Miami'
'Berlin'
'London'



No comments: