Search with multiple parameters in SQL Server

Question

This question is related to both c# and SQL Server.

I want to figure out how to do a custom search.

I have three tables

Customer

CusId, Name, Telephone

Employee

EmpId, Name, Job

Appointment

AppId, Date, EmpId, CusId

My C# form has three checkboxes. I want to find the data according to those checked values.

Ex: when customer,employee,app check boxes have selected, I want to find data on depending on all those three values.

When only two or one is selected I want to search depending on those selection. Here there will be total 6 combinations.

How to write a query to get correct result when I pass those values as parameters to a stored procedure.

Do I have to write 6 stored procedures to get the result?

Are there any methods to do this easily?

Please help me to fix this matter. Thanks in advance.

What have you tried already, how are you accessing the database from C# (entity framework, ADO.NET ...), can you use stored procedures, are you composing a text query (with parameters to prevent SQL injection)? All this may change the answer, so telling us what you have tried will help answer the question. — Andy Brown
– Andy Brown, Commented Feb 11, 2014 at 15:49
If you use EF, this is trivial. If you don't, it's still easy but requires more code. How do you access the DB ? On a side note, 3 options make for 8 combinations, not 6 — Alex
– Alex, Commented Feb 11, 2014 at 15:53

Paddy · Accepted Answer · 2014-02-11 15:55:28Z

1

With a query such as the below (would suggest in a stored proc):

-- Parameters to a SQL sproc
DECLARE @CustID INT, @EmpID INT, @AppointmentID INT

-- Set Parameters here for testing

SELECT *
FROM Appointment as A
INNER JOIN Employee as E
  ON E.EmpID = A.EmpId
INNER JOIN Customer as C
  ON C.CusID = A.CusID
WHERE (@CustID IS NULL OR C.CUsID = @CustID)
AND (@EmpID IS NULL OR E.EmpID = @EmpID)
AND (@AppointmentID IS NULL OR A.AppID = @AppointmentID)

You then need to pass in the parameters appropriately, either an ID if selected, or null if not filtering on one item.

answered Feb 11, 2014 at 15:55

Paddy

34k15 gold badges82 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Search with multiple parameters in SQL Server

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