CODEWITHSUNDEEP
sqlsql-servert-sql
user1572257
user1572257

Reputation: 97

SQL with clause dynamic where parameter

`SELECT * FROM [case]
WHERE status  = '0'
and date_rec > '01 January 2012'
and 'ClientFilter' = 
CASE @clientcode 
    WHEN '' THEN 'ClientFilter' 
    ELSE
CASE WHEN client in 
(SELECT ID COLLATE DATABASE_DEFAULT FROM
GREEN.dbo.fnSplitter(@clientcode)) THEN 'ClientFilter' END  END; `

I want to add an extra filter to the above query similar to the clientfilter. Does anyone know how to do this. As an example should be as below but does not work.

`SELECT * FROM [case]
WHERE status  = '0'
and date_rec > '01 January 2012'
and 'ClientFilter' = 
CASE @clientcode 
    WHEN '' THEN 'ClientFilter' 
    ELSE
CASE WHEN client in 
(SELECT ID COLLATE DATABASE_DEFAULT FROM GREEN.dbo.fnSplitter (@clientcode))
THEN 'ClientFilter' END  END;
and 'SchemeFilter' = 
CASE @schemecode  WHEN '' THEN 'SchemeFilter' 
ELSE
CASE WHEN client in 
(SELECT ID COLLATE DATABASE_DEFAULT FROM GREEN.dbo.fnSplitter(@schemecode))
THEN 'SchemeFilter' END  END; `

Upvotes: 1

Views: 677

Answers (1)

user1572257
user1572257

Reputation: 97

I have found the solution.
This way, a whole lot of conditional filters can be applied:

SELECT * FROM [case]
WHERE status  = '0'
AND date_rec > '01 January 2012'
AND 'SchemeFilter' = 
CASE @schemecode 
    WHEN '' THEN 'SchemeFilter' 
    ELSE
    CASE WHEN scheme in (SELECT ID COLLATE DATABASE_DEFAULT FROM GREEN.dbo.fnSplitter  (@schemecode)) THEN 'SchemeFilter' END  END
AND 'ClientFilter' = 
CASE @clientcode 
    WHEN '' THEN 'ClientFilter' 
    ELSE
    CASE WHEN client in (SELECT ID COLLATE DATABASE_DEFAULT FROM GREEN.dbo.fnSplitter(@clientcode)) THEN 'ClientFilter' END  END
AND 'Holderfilter' = 
CASE @holdercode
    WHEN '' THEN 'Holderfilter' 
    ELSE
    CASE WHEN holder in (SELECT ID COLLATE DATABASE_DEFAULT FROM GREEN.dbo.fnSplitter (@holdercode)) THEN 'Holderfilter' END  END
AND 'Officefilter' = 
CASE @officecode
    WHEN '' THEN 'Officefilter' 
    ELSE
    CASE WHEN [current] in (SELECT ID COLLATE DATABASE_DEFAULT FROM  GREEN.dbo.fnSplitter(@officecode)) THEN 'Officefilter' END  END

Upvotes: 2

Related Questions