troubleshooting Question

t-sql use CASE statement in WHERE clause

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL ServerSQL
1 Comment1 Solution89 ViewsLast Modified:
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


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]


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?
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros