Need Help with SQL query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help with SQL query

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-01 : 15:20:23

I have 4 parameters in the report e.g. id,emp_name,emp_dept,emp_loc with a textbox for each of them..

now the report should be generated using the search string created by values entered into these four textboxes using wild cards..

e.g.

if user enters emp_id as 1 then select * from employee where id like (%1%)

if user enters emp_dept as 'sales' then select * from employee where emp_dept like (%'sales'%)

now user can enter emp_name and emp_dept or any other combination or also all the four attributes..so search string needs to be configured accordingly..can somebody please suggest me how to do this..pls

really need ur help guys.. :(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-01 : 15:28:02

There are a few different ways to do this - Erland Sommerskog's blog here lists some of those: http://www.sommarskog.se/dyn-search-2005.html He calls it dynamic search conditions.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-01 : 23:18:35

Thank you so much my friend.
Unfortunately, I am not allowed to use EXECUTE or SP_EXECUTESQL statements on the db. So, is there any other way?

sshelper
Posting Yak Master

216 Posts

Posted - 2011-07-02 : 02:24:04

Try this query:

SELECT *
FROM [dbo].[Employee]
WHERE [ID] LIKE '%' + @ID + '%' AND
[Emp_Dept] LIKE '%' + @EmpDept + '%' AND
[Emp_Name] LIKE '%' + @EmpName + '%' AND
[Emp_Loc] LIKE '%' + @EmpLoc + '%'

With this query, even if the user doesn't enter anything to the @ID, @EmpDept, @EmpName or @EmpLoc, it will be replaced '%%' and will still return rows in your search.

Regards,
SQL Server Helper
http://www.sql-server-helper.com/es/mensajes-de-error/mensaje-1-500.aspx

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2011-07-02 : 03:26:04

thanks for the input mate..but your solution will not yield correct result..what if user doesnt enter anything..then @EmpName will be '' and also what if its null..in those cases the query will yield incorrect result

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-02 : 07:05:44

Erland's page describes methods that do not use dynamic SQL. He just calls the problem "dynamic search conditions", you don't necessarily have to use dynamic SQL for it.

Here is an approach (which he discusses), which is very simple, and would be the first thing that comes to mind for most programmers. Unfortunately, it would be one of the most inefficient too. But if you have only a few rows in your table, you may be able to get away with it. I am modifying SSHelper's code here:


SELECT *
FROM [dbo].[Employee]
WHERE 
   ([ID] LIKE '%' + @ID + '%' or @ID IS NULL)
   AND ([Emp_Dept] LIKE '%' + @EmpDept + '%' OR @EmpDept IS NULL)
   AND ([Emp_Name] LIKE '%' + @EmpName + '%' OR @EmpName IS NULL)
   AND ([Emp_Loc] LIKE '%' + @EmpLoc + '%' OR @EmpLoc IS NULL);

If you have a large number of rows or if it is a heavily used query, please do read through Erland's blog.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources