sql server - Create a sql query that can handle multiple check box selections

Question

I have a form with 3 check box dropdown lists enabling multiple selection from each control.

Lets say for talking sake its an accommodation table I am querying and the check box dropdown lists are 'AccommodationName', 'Company', and 'Nights'.

So potentially I could be passing in multiple values from each control and I want to return an aggregated query relevant to all data input.

How should I be going about this query?

Is the query going to have to be dynamic sql?

Please note, I am using sql server 2005.

Answer 1

You will need to create a split function inside you database,

Definition Of Split Function

CREATE FUNCTION [dbo].[split]
    (
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
    ) 
 RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

Stored Procedure

Then you need to create a stored procedure which will build sql query dynamically and use this split function to handle multiple values passed as a comma deliminated list.

CREATE PROCEDURE GetData
    @AccommodationName  VARCHAR(1000) = NULL,
    @Company            VARCHAR(1000) = NULL,
    @Nights             VARCHAR(1000) = NULL
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @SQL NVARCHAR(MAX);

 SET @SQL = N' SELECT * FROM TableName WHERE 1 = 1 '
            + CASE WHEN @AccommodationName IS NOT NULL 
                THEN N' AND AccommodationName IN (SELECT Val FROM dbo.split(@AccommodationName )) '
                    ELSE N'' END
            + CASE WHEN @Company IS NOT NULL 
                THEN N' AND Company IN (SELECT Val FROM dbo.split(@Company)) '
                    ELSE N'' END
            + CASE WHEN @Nights IS NOT NULL 
                THEN N' AND Nights IN (SELECT Val FROM dbo.split(@Nights)) '
                    ELSE N'' END

  EXECUTE sp_executesql @SQL
                       ,N'@AccommodationName VARCHAR(1000), @Company VARCHAR(1000), @Nights VARCHAR(1000)'
                       ,@AccommodationName 
                       ,@Company 
                       ,@Nights 
END