We help IT Professionals succeed at work.

t-sql use CASE statement in WHERE clause

maqskywalker
maqskywalker asked
on
i'm using sql server 2019.

I have a table that looks like this:

Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesReport](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] ON 
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (1, N'Davolio', N'Nancy')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (2, N'Fuller', N'Andrew')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (3, N'Leverling', N'Janet')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (4, N'Peacock', N'Margaret')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (5, N'Buchanan', N'Steven')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (6, N'Suyama', N'Michael')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (7, N'King', N'Robert')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (8, N'Callahan', N'Laura')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (9, N'Dodsworth', N'Anne')
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] OFF
GO

Open in new window



So far i have this query:

DECLARE @IsCurrentMonth VARCHAR(100), @LastName VARCHAR(100), @FirstName VARCHAR(100)
SET @LastName = 'Fuller'
SET @FirstName = 'Laura'
SET @IsCurrentMonth = 'Yes'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[EmployeesReport]

Open in new window



for the WHERE clause I want to use an CASE statement or if else statement.


What i need is the following,

if the parameter called @IsCurrentMonth equals 'Yes' i want the where clause to look like this. Filter by LastName.

-- if @IsCurrentMonth equals Yes use this where clause
WHERE [LastName] = @LastName



if the parameter called @IsCurrentMonth equals 'No' i want to the where clause to look like this. Filter by FirstName.

-- if @IsCurrentMonth equals No use this where clause
WHERE [FirstName] = @FirstName


I know I can just put the whole query inside an if statement, but I know what to apply the CASE statement to the WHERE clause.

What's the best syntax for this?
Comment
Watch Question

View Solution Only

Data Warehouse / Database Architect
SILVER EXPERT
Commented:
Case returns a value.  It's wordy and tricky to do what you're asking.

Much simpler would be to do something like this:

WHERE (isCurrentMonth = 'Yes' and LastName = @LastName)
  OR (isCurrentMonth = 'No' and FirstName = @FirstName)