vb.net dynamic SQL String

Hi Experts,

I am trying to see if there is a way where I can complete this search via my VB.Net application, or SQL Server without having multiple if functions

The application has a selection criteria, however it is not compulsory that every item is filled out depending on the search they want to complete.

the SQL that would complete this is

select  w.WorkOrderNumber, w.WorkOrderType, r.result,
d.DeptDescription, u.UserID, w.DatetobeStarted, w.DatetobeFinished 
from WorkOrders.WorkOrders w 
left join Risks.RAM  r on r.RiskID = w.RiskID 
left join WorkOrders.Dept d on d.DeptID = w.DeptID 
left join Users.Users u on w.Assignedto = u.UserIDNumber 
(u.UserIDNumber is null or (UserIDNUmber = 1003)) 
and (d.DeptID is null or(d.DeptID  = 1))
and (w.FaultID is null or (w.FaultID = 4))
and (r.Result  is null or (r.Result = 2))
and (w.Status is null or (w.Status = 9))
and (w.DatetobeStarted is null or (w.DatetobeStarted = '2014-03-04'))
and (w.DatetobeFinished is null or (w.DatetobeFinished = '2014-03-04'))

Open in new window

What I am trying to avoid is multiple if statements \ conditions for every possibility of the search

Who is Participating?
slinkygnConnect With a Mentor PresidentCommented:
Not sure you can avoid that -- a conditional is a conditional.  Obviously you don't need an if/case with the full SQL for every possibility; you can just construct the string and append certain parts of the string (each of the "and"s in the WHERE clause) with the ifs -- so sqlString=sqlString + (deptIdIsFilledIn() ? "and d.DeptId = 1" : "") or the like -- but if there's a way to avoid conditionals altogether it's not coming to me.
SimonPrice33Author Commented:
that's what I was hoping to be able to avoid... ohwell..

lots of conditions to write for me then...

thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.