SQL condition based on variable value-CodePudding

I have to perform a query and it's where condition should depend on variable value

for example if variable @EventId = 0 then condition should be

select * from tbl_event where event_id != @EventId

And If variable @EventId = 10 then condition should be

select * from tbl_event where event_id = @EventId

I have to do this in MSSQL database store procedure can anyone help me out

CodePudding user response：

Condition 1: Match @EventId = 0 and event_id != @EventId

Condition 2: Match @EventId = 10 and event_id = @EventId

To fulfill either of these conditions, you need OR operator.

SELECT * 
FROM tbl_event 
WHERE (@EventId = 0 AND event_id != @EventId)
  OR (@EventId = 10 AND event_id = @EventId)

CodePudding user response：

Try this:

select * from tbl_event 
where Case 
          When @EventId = 0 AND Event_id != 0 THEN 1
          When @EventId = 10 AND Event_id = 10 THEN 1
          ELSE 0
      END = 1

CodePudding user response：

 SELECT 
   * 
 FROM 
  tbl_event 
WHERE 
  (
    @EventId = 0 
    AND event_id <> @EventId
  ) 
  OR 
  (
    @EventId = 10 
    AND event_id = @EventId
  )