Problem while applying database WHERE clause.

Question

0.00/5 (No votes)

See more:

Hi all,

Question description :

On my application UI, there are two filter Filter1 (Internal Contact) and Filter2 (My Contact Activities), filter can we selected through UI, either both or any of one.

In the database, there is a table tbl_Contacts having a column EmployeeID,ContactName,RoleType, Email, Location, Office etc., selected filter ("Internal Contact" or "My Contact Activities") needs to be apply on EmployeeID and RoleType.

When user select "Internal Contact" filter then it will apply like "EmployeeID = @InternalContact" but when user select "My Contact Activites" filter then it will apply like "EmployeeID = @MyContactActivities and RoleType = 'AccountTemaMember'".

Internal Contact can be any user selected from filter1 and my contact activities can be any user selected from filter2.

Other filter also can be applied and its not mandatory that InternalContact and MyContactActivities filter will be applied.
The result set should contact both user (selected from filter1 and selected from filter2) information.

What will the best way to apply WHERE clause in the stored procedure? excuse for dynamic query.

Posted 2-Mar-13 6:21am

Mahendra Vishwakarma

Updated 2-Mar-13 8:01am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-03-02T07:12:00

Solution 1

Hi,

Check the following Code

SQL

DECLARE @InternalContact INT,@MyContactActivities INT
 
SELECT @InternalContact=10
SELECT  EmployeeID,ContactName,RoleType, Email
FROM tbl_Contact
WHERE ( ((@InternalContact IS NOT NULL AND @MyContactActivities IS NULL AND EmployeeID=@InternalContact) OR @InternalContact IS NULL)
AND ((@MyContactActivities IS NOT NULL AND @InternalContact IS NULL AND EmployeeID=@MyContactActivities AND RoleType = 'AccountTemaMember') OR @MyContactActivities IS NULL))
OR (@InternalContact IS NOT NULL AND @MyContactActivities IS NOT NULL AND(EmployeeID=@InternalContact OR (EmployeeID=@MyContactActivities AND RoleType = 'AccountTemaMember')))

Regards,
GVPrabu

Posted 2-Mar-13 7:12am

gvprabu

Updated 2-Mar-13 7:26am

v2

Comments

Mahendra Vishwakarma 2-Mar-13 13:21pm

Thanks for replying me! but this will not work because suppose we have selected both filter @InternalContact and @MyContactActivities from UI then it will not give you any result if both filter are having different employeeid, wehre as it should display both person details.

gvprabu 2-Mar-13 13:27pm

Hi If you have sample data, Send to me I ll check and give u the required Query....

Mahendra Vishwakarma 2-Mar-13 13:56pm

Please find the below sample data in tbl_Contacts having.

EmployeeID ContactName RoleType Email Location Office

12 A ATM A@gmail.com India Delhi

13 B ATM B@gmail.com US Montevale

14 C ATM C@gmail.com UK Londan

15 D AM D@gmail.com UK Englend

There can also aplly other filters like Location or Offcies and its not mandatory that InternalConatct and MyContactActivites filter will be applied.

Taha Akhtar · Answer 2 · 2013-03-02T09:37:00

Solution 3

SQL

Create proc Cntct
@InternalContact int=-1,
@MyContactActivities int=-1
as
BEGIN

select * from tbl_Contact
where (isnull(@InternalContact,-1)!=-1 and EmployeeID=@InternalContact) OR
  (isnull(@MyContactActivities,-1)!=-1 and  EmployeeID=@MyContactActivities AND RoleType = 'ATM')
OR (isnull(@MyContactActivities,-1)=-1 and isnull(@InternalContact,-1)=-1)

END

select all records any filter are not selected

if filter is not selected then pass -1 as default or dont pass that parameter

Posted 2-Mar-13 9:37am

Taha Akhtar

Updated 4-Mar-13 7:42am

v4

Comments

Mahendra Vishwakarma 4-Mar-13 9:53am

Thanks for posting your answer but I dont think it will work as its not mandatory that user will select the filter, what if niether InternalContact nor MyContactActivity filter is selected. will above WHERE caluse will return the out put.

Taha Akhtar 4-Mar-13 13:40pm

see above improved solution, it will return all records

Mahendra Vishwakarma 5-Mar-13 12:35pm

Hello Sir, I tried but its not working!!

Taha Akhtar 6-Mar-13 1:57am

what parameters you are providing