I'm creating a search page on our SharePoint site that will allow users to find results from a SQL table by either performing a search from a text box @Param1, or selecting a value from a dropdown @SiteParam.

My SQL SelectStatement is

SELECT * FROM [Routing] 
    WHERE [Site] LIKE 
        CASE WHEN @Param1 IS NOT NULL 
            THEN '%' + @Param1 + '%' 
            ELSE @SiteParam 

This works great in SQL when I declare the parameters and set values for the search, but not so much in SharePoint.

I've been able to get it to work if I don't do the case and just use 1 of the parameters, but I haven't found a way to get it to do either of them.

Any help is appreciated! Thanks