Parametrise where clause in SQL Server 2008 R2

Question

I want to pass the parameter of stored procedure where clause from C#. I have declared a parameter @WhereClause nvarchar(max) and in query I have given like this

select distinct
    CLFIIK.AdID
    ,CLFIIK.AdGiverName
    ,CLFIIK.AdGiverEmail
    ,CLFIIK.Title
    ,left(CLFIIK.Descripton,200) +'...' as Descripton -- select 200 Caharacter and after 200 char it will add ...
    , case
          when CLFIIK.type = 1 then -- Wanted Events
            'Wanted Event'
          when  CLFIIK.type = 1 then -- offering Events
            'Offering Event'
          else -- Neither Wanted no Offering Events 
               'No Event Yet'
        end as Type 
         ,CASE
           WHEN DATEDIFF(HOUR, PublishedDate, GETDATE()) < 24 THEN 
               CASE DATEDIFF(HOUR, PublishedDate, GETDATE()) 
                   WHEN 1 THEN 
                      CONVERT(VARCHAR, DATEDIFF(HOUR, PublishedDate, GETDATE())) + ' hour ago' 
                   ELSE 
                      CONVERT(VARCHAR, DATEDIFF(HOUR, PublishedDate, GETDATE())) + ' hours ago'
               END
          ELSE 
         REPLACE(CONVERT(VARCHAR, PublishedDate, 6), ' ', '-')  
       END as PublishedDate
       ,CLFIIK.LastModifiedDate
       ,LM.LocalityName +', '+ CityM.CityName as Address
       ,CM.CategoryName
       ,CM.CategoryID
 from 
     CLF.utblCLFAdInstanceInfoKeys as CLFIIK
 inner join
     dbo.utblCategoryMaster as  CM on CM.CategoryID = CLFIIK.CategoryID
 inner join 
     dbo.utblLocalityMaster as LM on LM.LocalityID = CLFIIK.LocalityID
 inner join 
     dbo.utblCityMaster as CityM on CityM.CityID = LM.CityID
 left join 
     CLF.utblCLFAdInstanceDtls as AdInsDeets on AdInsDeets.AdID = CLFIIK.AdID
 where  
     @WhereClause;

SQL Server 2008 R2 throws this error

Msg 4145, Level 15, State 1, Procedure udspGetCLFSearchResultEventList, Line 55
An expression of non-boolean type specified in a context where a condition is expected, near ';'.

How can I fix this error?

This is the syntax for where clause: http://technet.microsoft.com/en-us/library/ms173545%28v=sql.105%29.aspx in order to be syntactically correct your variable should be boolean but that will not achieve what you want. The right way to do this is to either parametrize specific parts of your search predicate or convert your stored procedure to a view and then use something like entity framework on the front end to create the correct sql for you. — Andrew Savinykh, Apr 07 '14 at 05:45
It's better if you describe why would you want to pass the where clause as text, there may be better solutions. If not, than you can construct a character string query from your variable and then execute it with EXECUTE http://technet.microsoft.com/en-us/library/ms188332%28v=sql.105%29.aspx but I would advise against it — Andrew Savinykh, Apr 07 '14 at 05:47
cant I convert the nvarchar to boolean so that I can have expected output — Iswar, Apr 07 '14 at 05:47
Actually I want to have faceted search in my website. that is the only reason why I want to set the parameter in C# and pass — Iswar, Apr 07 '14 at 05:49
I have no experience with faceted search, but apparently [relational databases cannot implement faceting efficiently](http://stackoverflow.com/questions/1847909/efficient-implementation-of-faceted-search-in-relational-databases) — Andrew Savinykh, Apr 07 '14 at 05:54

smoore4 · Answer 1 · 2014-04-07T06:44:13.020

While it is true that this would work...

DECLARE @sql nvarchar(2000) = 'SELECT ....  FROM ....'
DECLARE @sqlwithwhere nvarchar(2000)
SET @sqlwithwhere = @sql + @WhereClause
EXEC sp_executesql @sqlwithwhere

...you should really declare stored procedure parameters in C# and pass them individually. There is a risk of SQL Injection with dynamic SQL like this. And it is more of a hack than a standard approach.

Parametrise where clause in SQL Server 2008 R2

1 Answers1