CODEWITHSUNDEEP
sql-servercase
Relativity
Relativity

Reputation: 6868

how can I have different where condition using case statement?

@HistoryMSBType => This is a variable which can contain any varchar.

Depending upon it's value, i need to have different type of where clause.

How can I achieve this ?

SELECT * FROM stageTable map
WHERE id = 1
CASE @HistoryMSBType
 WHEN 'Utilization Other' THEN
    AND map.ColumnID = 4
 WHEN 'Cost Other' THEN
    AND map.ColumnID = 6
 ELSE
    AND map.ColumnName = @HistoryMSBType
END

Upvotes: 0

Views: 252

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

SELECT *
FROM stageTable map
WHERE id = 1
AND (
  (@HistoryMSBType = 'Utilization Other' AND map.ColumnID = 4)
  OR
  (@HistoryMSBType = 'Cost Other' AND map.ColumnID = 6)
  OR
  (isnull(@HistoryMSBType,'') NOT IN ('Utilization Other','Cost Other')
   AND map.ColumnName = @HistoryMSBType)
  )

You need the ISNULL to make it match the CASE-ELSE exactly, but it won't matter if it can never be null.

Upvotes: 3

Related Questions