Add multiple filters to dynamic query in SQL Server

Last Reply 16 days ago By pandeyism

Posted 16 days ago

hello,

i am using this solution it works fine but i want to add another like filter in it

second filter i want to add is simple like '%'+@category+'%'

how to modify the below code to add another category filter

CREATE TABLE #animal (ID INT,type VARCHAR(max))
INSERT INTO #animal VALUES(1,'cat, lion, donkey,')
INSERT INTO #animal VALUES(2,'tiger, safari,')
DECLARE @Key VARCHAR(200)
SET @Key = 'cat,tiger'
SELECT @Key = 'LIKE ''%'+ REPLACE(@Key,',','%'' OR type LIKE ''%')+'%'''
EXEC('SELECT * FROM #animal WHERE type '+ @Key +'')
DROP TABLE #animal

 

Posted 16 days ago

Hey nauna,

Please refer below query.

SQL

CREATE TABLE #animal (ID INT,type VARCHAR(max),category VARCHAR(max))
INSERT INTO #animal VALUES(1,'cat, dog,','pet')
INSERT INTO #animal VALUES(2,'tiger, safari,','wild')

DECLARE @Key VARCHAR(200),@category VARCHAR(200)
SET @Key = 'cat,'
SET @category = 'pet'
SELECT @Key = 'LIKE ''%'+ REPLACE(@Key,',','%'' OR type LIKE ''%')+'%'''
EXEC('SELECT * FROM #animal WHERE (type '+ @Key +') AND category LIKE ''%'+@category+'%''')
DROP TABLE #animal

Output

ID type category
1 cat, dog, pet