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?