Dynamic query in WHERE Condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic query in WHERE Condition

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2014-09-10 : 02:41:04

I do have a table type as input
---------------------------
num |columnname |value
---------------------------
1 |Name |3X5900
2 |employeeID |null
3 |JOD |null
4 |Address |null

From this I have to form the where clause like "Name like'3X5900' " .This will be dynamic , as the search may vary on the next call for serch.
How I have to implement this dynamic query in the below mentioned code.I have add the dynamic query where i have mentioned as '---- Where clause has to be added '.Some time the table input will be having all the value for search.How i can implent this in my query with good performance.

DECLARE @Where varchar(4000);
Select @where = 'v.Name LIKE ''3X5900'''
SELECT * from
(SELECT Row_number()Over(Order By V.ProjectId) Rownum,
v.Firstname,
v.secondNmae,
v.address,
v.CompanyId
FROM v_employeeInfo v
LEFT JOIN salary b2 ON v.employeeId = b2.employeeId
LEFT JOIN Domain d ON b2.DomainId = d.DomainId
---- Where clause has to be added
)SearchTest
where Rownum < 501

order by Firstname

Regards,
Divya

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 09:07:22

There's a good article on this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources