Passing CSV as parameter

[TonyBoex]

I have an ASP page which has several dropdown boxes which allow the user to select multiple items. I have been trying to get the stored procedure to return the filtered recordset but am having trouble with the appropriate use of apostrophies in the string parameters. Also, is my use of the COALESCE function possible when the parameter is a CSV?



Alter Procedure "spMishapCount_Filtered_IN"
(
@AC_Type varchar(100) = NULL,
@Mishap_Type varchar(30) = NULL,
@Mishap_Class varchar(10) = NULL,
@Location varchar(100) = NULL,
@Service varchar(100) = NULL,
@Year varchar(100) = NULL
)
As

Set nocount on

--Insert filtered data into Temp Filter_Table
SELECT MishapID INTO #tblTemp_Filter_Table
FROM tblMishaps
WHERE tblMishaps.Aircraft_FK IN (COALESCE(@AC_Type, tblMishaps.Aircraft_FK)) AND
tblMishaps.Type_FK IN (COALESCE(@Mishap_Type, tblMishaps.Type_FK)) AND
tblMishaps.Class_FK IN (COALESCE(@Mishap_Class, tblMishaps.Class_FK)) AND
tblMishaps.LocationID_FK IN (COALESCE(@Location, tblMishaps.LocationID_FK)) AND
tblMishaps.OrgID_FK IN (COALESCE(@Service, tblMishaps.OrgID_FK)) AND
CAST(Year(DateAdd(month,3,tblMishaps.MishapDate)) AS varchar(100)) IN (COALESCE(@Year, CAST(Year(DateAdd(month,3,tblMishaps.MishapDate)) AS varchar(100)))) AND
NOT(tblMishaps.DatabaseType = 'C')

 

[tlbroadbent]

It is not possible to use the parameter directly in the SQL statement as you have done. You'll need to dynamically create your SQL statement and the execute it. Therefore, the COALESCE function will not work.

You could do something like the following.

SET @sql='SELECT MishapID INTO #tblTemp_Filter_Table
FROM tblMishaps
WHERE tblMishaps.DatabaseType <> ''C''' +
CASE
WHEN @AC_Type IS NOT NULL
THEN char(10) + ' AND tblMishaps.Aircraft_FK IN (' + @AC_Type + ')'
ELSE ''
END +
CASE
WHEN @Mishap_Type IS NOT NULL
THEN char(10) + ' AND tblMishaps.Type_FK IN (' + @Mishap_Type + ')'
ELSE ''
END +
CASE
WHEN @Mishap_Class IS NOT NULL
THEN char(10) + ' AND tblMishaps.Class_FK IN (' + @Mishap_Class + ')'
ELSE ''
END +
CASE
WHEN @Location IS NOT NULL
THEN char(10) + ' AND tblMishaps.LocationID_FK IN (' + @Location + ')'
ELSE ''
END +
CASE
WHEN @Service IS NOT NULL
THEN char(10) + ' AND tblMishaps.OrgID_FK IN (' + @Service+ ')'
ELSE ''
END +
CASE
WHEN @YEAR IS NOT NULL
THEN char(10) + ' AND YEAR(DateAdd(month,3,tblMishaps.MishapDate)) IN (' + @Year + ')'
ELSE ''
END

EXECUTE @sql

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

If you are passing string parameters, you'll need to use two single quotes to represent each single quote in the string. For example, @location = '''Bin1'',''Bin3'',''Bin9''' which would become IN ('Bin1','Bin3','Bin9') in the @sql statement. Your ASP page will need to add the extra single quotes before passing the strings to SQL Server. Terry Broadbent

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin