SQL with clause dynamic where parameter

Question

`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; `

Pang · Accepted Answer · 2016-06-12 05:46:48Z

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

Collectives™ on Stack Overflow

SQL with clause dynamic where parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related