CODEWITHSUNDEEP
c#sql-serverasp.net-coreasp.net-web-api
trx
trx

Reputation: 2167

Dynamically change the where clause in the SQL Query

I building Angular application where I need to call the .net core API and return result. There are two fields in the angular application like below

enter image description here

Both the fields can be selected , may be just one field will be selected and result needs to be fetched from the API depending on the selection

My Query like below if both the fields are selected

  Select prj_number,location from sTable where r_proj = '000634' AND sdate = '2019-07-01' group by prj_number,location

And if just reporting project is just selected then 

 Select prj_number,location from sTable where r_proj = '000634'  group by prj_number,location

Likewise for duration, Dynamically Change the where clause depending on the selection made from Angular.

I thought of building a string expression in Angular depending on the selection like 

     string para = r_proj = '000634' AND sdate = '2019-07-01'

and pass into the Query like 

  Select prj_number,location from sTable where @para  group by prj_number,location

But this didnt work, I get error like 'An expression of non-boolean type specified in a context where a condition is expected, near 'group'.'and looks like I cant pass expression as a parameter. How can I approach this scenario

Upvotes: 0

Views: 544

Answers (1)

MPost
MPost

Reputation: 535

Make each parameter "optional" as follows:

DECLARE @projIdFilter varchar(10) = null
DECLARE @sdateFilter datetime = null

SELECT prj_number,location 
FROM sTable 
WHERE (@projIdFilter is null or r_proj = @projIdFilter) -- parenthesis are important
  AND (@sdateFilter is null or sdate = @sdateFilter)
GROUP BY prj_number,location

Upvotes: 4

Related Questions