Multiple Multi Select And T SQL

I'm looking to see if there are any best known methods or better ways of handling multiple multi select parameters in reports coming from reporting services.

It's not a big deal to account for this in T-SQL when there are only a few multi-select boxes but each new one that I add creates a whole bunch more work for me. Each of these multiselect list boxes are optional filters for the report. The way that I"m handling it today is as follows:

I'm turning the multi-select lists into comma seperated variables and then using Itzik Ben-Gan's split UDF to turn them into tables that I can join to my query. I then take the base query w/out the joins and pass that into a temp table. Then I start a bunch of IF THEN program flow to either JOIN or not JOIN the results of the split UDF.

Here is my example scenarios:
Assumptions:


I have a report that has 5 input drop down lists in reporting services.

I have a user stored procedure that takes the multi-select comma seperated list and turns it into a table variable.

I cannot use Dynamic SQL because of information security policy.

The multi-select lists are optional parameters.

The Code below is accurate.... I didn't test it and just wrote it quickly to show the complexity... hopefully you get the idea.


In scenario 1: One of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:

CREATE PROCEDURE [dbo].[get_my_ssrs_data]

@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''

WITH RECOMPILE

AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;

SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID

INTO #MyCoreTemp

FROM
My_Fact_Table

IF @Origin_Warehouse_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END


In scenario 2: Two of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:

CREATE PROCEDURE [dbo].[get_my_ssrs_data]

@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''

WITH RECOMPILE

AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;

SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID

INTO #MyCoreTemp

FROM
My_Fact_Table

IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END


In scenario 3: Three of the drop down lists are multi-select enabled.
My SQL Sproc Would look like this:

CREATE PROCEDURE [dbo].[get_my_ssrs_data]

@Origin_Warehouse_IDs varchar(max) = '',
@Ship_To_Geo_IDs varchar(max) = '',
@Sold_To_Geo_IDs varchar(max) = '',
@Customer_Type_IDs varchar(max) = ''

WITH RECOMPILE

AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#MyCoreTemp', N'U') IS NOT NULL DROP TABLE #MyCoreTemp;

SELECT
Origin_Warehouse_ID
, Ship_To_Geo_ID
, Sold_To_Geo_ID
, Customer_Type_ID

INTO #MyCoreTemp

FROM
My_Fact_Table

IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs != '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs = '' AND @Customer_Type_IDs != ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END
ELSE
IF @Origin_Warehouse_IDs = '' AND @Ship_To_Geo_IDs != '' AND @Customer_Type_IDs = ''
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
END
ELSE
BEGIN
SELECT * FROM #MyCoreTemp MCT
JOIN udfSplit(@Origin_Warehouse_IDs,DEFALUT) OW ON (MCT.Origin_Warehouse_ID = OW.nstr)
JOIN udfSplit(@Ship_To_Geo_IDs,DEFALUT) STG ON (MCT.Ship_To_Geo_ID = STG.nstr)
JOIN udfSplit(@Customer_Type_IDs,DEFALUT) CT ON (MCT.Customer_Type_ID = CT.nstr)
END



END


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

As you can see by scenario 3 where there are only 3 multi-select lists it gets really hairy. If I go to a forth it will be even worse. Does anyone know of another better way to do this?


Thanks a bunch in advance!
Letni