MSSQL Stored Procedure with dynamically added WHERE?

Question

I have this Stored Procedure, where i need to check if @DateChk == 1, THEN i want to add a WHERE-clause to the SQL statement in the SP. If its 0, there should not be any WHERE-clause.

How do i script such function? In a program i could just build in the string to pass to the server, but i have not found any way to do this in the SQL server.

SELECT  dateDay, SUM(nok) as NOK, SUM(ok) as OK,  (SUM(ok) + SUM(nok)) as 'Total'
FROM #st
SELECT CASE @DateChk
WHEN 1: WHERE dateDay BETWEEN '2016-08-01' AND '2016-08-31'

Something like this, if @DateChk = 1, the WHERE should be added, else fetch all records.

Andrey Korneyev · Accepted Answer · 2016-12-07 09:21:23Z

3

You can modify your condition like this:

SELECT  dateDay, SUM(nok) as NOK, SUM(ok) as OK,  (SUM(ok) + SUM(nok)) as 'Total'
FROM #st
WHERE 
(@DateChk = 1 and (dateDay BETWEEN '2016-08-01' AND '2016-08-31'))
or @DateChk = 0

And it will exactly match your desired behaviour: condition on dateDay will be applied only when @DateChk = 1.

edited Dec 7, 2016 at 9:21

answered Dec 7, 2016 at 9:18

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

4 Comments

Stefan Grönberg Over a year ago

Thanks, this helped tremendous.

Stefan Grönberg Over a year ago

Another question. if i have 2 more BITs i want to do IF on? if the DateChk = 0, but ArtNrChk is 1, or if both are 1 etc? I currently have 4 checkboxes to select what to display/filter on.

Andrey Korneyev Over a year ago

@StefanGrönberg then same logic can be applied. Just modify your where condition according to new logic, like "((some_condition) and (additional condition to apply)) or (negation of some_condition)"

Stefan Grönberg Over a year ago

Right, thanks once again @Andy Korneyev. Starting to get hand of this and-oring, and it works nicely now.

Collectives™ on Stack Overflow

MSSQL Stored Procedure with dynamically added WHERE?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related