Can I add dynamic WHERE clause to this query?

Chikwe · May 12, 2025, 7:45pm

Greetings again experts,

I hope to get one more assistance.

The following query works with or without WHERE clause.

What this query does is create a Pivot table that displays YEAR as column headers.

Then it displays records for each user from the current year dating back to when the users started making donations.

If you the query without the following WHERE clause, it displays all the records dating back to 2012 when the donation campaign started.

-- WHERE employeeName LIKE ''%' + @ename + '%'' ```

With the WHERE clause in, it displays only the record that matches the WHERE predicate.

The code works pretty good.

However, we have been tasked with making the code work both with the WHERE clause and without it.

In other words, on page load, all the records are displayed but if user wants to filter records based on specific search criterium, it should display records associated with your search value of the record exists.

I have attempted to comment out the initial WHERE clause where I placed it and tried to replace it with the following:

If @ename Is Not Null
Set @query = @query + ' WHERE (employeeName = @ename)'
Else
Set @query = @query

When I test it, I keep getting error that @ename is not declared.

Any way to modify this query to display records when no parameter value is provided and when it is provided?

Here is the entire query and many thanks as usual in advance.

ALTER PROCEDURE [dbo].[uspGetPivotedDonors]
@ename AS NVARCHAR(75)

AS
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy'))
FROM DateDetails d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT employeeName,empTitle,email, ' + @cols + ' from
(
select employeeName
,empTitle
,email
,Year(dateCreated) AS YearSubmitted
from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
-- WHERE employeeName LIKE ''%' + @ename + '%''
) x
pivot
(
max(YearSubmitted)
for YearSubmitted in (' + @cols + ')
) p '
If @ename Is Not Null
Set @query = @query + ' WHERE (employeeName LIKE ''%' + @ename + '%'')'
Else
Set @query = @query

khtan · May 13, 2025, 1:42am

Firstly, for debugging purposes, add a print to display the final query.

PRINT @query

Remove the following line, though you have masked it in the dynamic query, but when you pass in NULL to @ename, your @query will be NULL

harishgg1 · May 13, 2025, 1:52pm

hi

hope this helps

test data

-- Create test tables  
CREATE TABLE Employees (  
    EmployeeID INT PRIMARY KEY,  
    employeeName NVARCHAR(100),  
    empTitle NVARCHAR(100),  
    email NVARCHAR(100)  
);  

CREATE TABLE SourceDetails (  
    SourceID INT PRIMARY KEY,  
    employeeID INT,  
    donationAmount DECIMAL(10, 2),  
    FOREIGN KEY (employeeID) REFERENCES Employees(EmployeeID)  
);  

CREATE TABLE DateDetails (  
    DateID INT PRIMARY KEY,  
    employeeID INT,  
    dateCreated DATE,  
    FOREIGN KEY (employeeID) REFERENCES Employees(EmployeeID)  
);  

-- Insert test data  
INSERT INTO Employees (EmployeeID, employeeName, empTitle, email)  
VALUES  
(1, 'John Doe', 'Development Manager', 'john.doe@company.com'),  
(2, 'Jane Smith', 'Marketing Director', 'jane.smith@company.com'),  
(3, 'Bob Johnson', 'Sales Manager', 'bob.johnson@company.com'),  
(4, 'Alice Brown', 'IT Director', 'alice.brown@company.com');  

INSERT INTO SourceDetails (SourceID, employeeID, donationAmount)  
VALUES  
(1, 1, 500.00),  
(2, 1, 750.00),  
(3, 2, 300.00),  
(4, 2, 600.00),  
(5, 3, 400.00),  
(6, 3, 800.00),  
(7, 4, 250.00),  
(8, 4, 900.00);  

INSERT INTO DateDetails (DateID, employeeID, dateCreated)  
VALUES  
(1, 1, '2020-01-15'),  
(2, 1, '2021-06-20'),  
(3, 2, '2022-03-10'),  
(4, 2, '2023-08-25'),  
(5, 3, '2021-02-05'),  
(6, 3, '2024-09-30'),  
(7, 4, '2023-04-15'),  
(8, 4, '2025-05-10');

SP

ALTER PROCEDURE [dbo].[uspGetPivotedDonors]
    @ename AS NVARCHAR(75) = NULL
AS
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy'))
                    FROM DateDetails d
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
                ,1,1,'')

set @query = N'SELECT employeeName,empTitle,email, ' + @cols + ' from
(
    select employeeName
    ,empTitle
    ,email
    ,Year(dateCreated) AS YearSubmitted
    from SourceDetails 
    inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
    inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
    WHERE employeeName LIKE ''%' + @ename + '%''
) x
pivot
(
    max(YearSubmitted)
    for YearSubmitted in (' + @cols + ')
) p'

IF @ename IS NULL
BEGIN
    SET @query = REPLACE(@query, ''WHERE employeeName LIKE ''%' + @ename + '%' '', '')
END

EXEC sp_executesql @query

please let me know "any mistakes"
lets discuss get to the final solution

Chikwe · May 13, 2025, 6:18pm

Thank you sir for your help but I don't understand the changes you made.

I can't even get it to run. It has errors on the REPLACE(...) line.

Chikwe · May 13, 2025, 6:21pm

@khan , the code works fine with or without the WHERE clause.

I am not sure why you want me to put a PRING @query.

What is not working is when I try to use dynamic where clause where the query should display all the records if WHERE is blank or null or it displays only the record associated with the WHERE condition.

I have even browsed the net and seen sample that look like my query that they say works but same dynamic where wouldn't work for me.

Thank you very much for your response.

UPDATE: I see why you wanted to print the query to see what it shows.

This is what it shows, followed by the error that accompanies it.

SELECT employeeName,empTitle,email, [2018],[2019],[2020],[2023],[2024],[2025] from 
            (
                select employeeName
                    ,empTitle
					,email
                    ,Year(dateCreated) AS YearSubmitted
                from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
                WHERE 1 = 1
		   ) x
            pivot 
            (
                 max(YearSubmitted)
                for YearSubmitted in ([2018],[2019],[2020],[2023],[2024],[2025])
            ) p  And (employeeName LIKE '%' + @ename + '%') 


Msg 156, Level 15, State 1, Line 17

Incorrect syntax near the keyword 'And'.

harishgg1 · May 13, 2025, 9:44pm

thanks for your honest reply

will work on it and get back to you

khtan · May 14, 2025, 2:23am

the PRINT is for you to verify the dynamic generated query and identify the issue.

In your dynamic query, when you pass in NULL to @ename, the final @query is actually NULL. This is due to the concatenation you have in the below line.

If you have the PRINT @query statement, you will be able to notice the mistake easily.

On the above error message, it is the output of your modified dynamic query. NOT the one that you have posted here.

Any here is the modified query. It should works for pass in @ename with value or NULL

ALTER PROCEDURE [dbo].[uspGetPivotedDonors]
    @ename AS NVARCHAR(75)
AS
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy'))
FROM DateDetails d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT employeeName,empTitle,email, ' + @cols + ' from
(
select employeeName
,empTitle
,email
,Year(dateCreated) AS YearSubmitted
from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
) x
pivot
(
max(YearSubmitted)
for YearSubmitted in (' + @cols + ')
) p '
If @ename Is Not Null
  Set @query = @query + ' WHERE (employeeName LIKE ''%' + @ename + '%'')'
Else
  Set @query = @query

PRINT @query  -- for debugging only. Remove it for live

EXEC (@query)

Chikwe · May 14, 2025, 3:06am

@khtan , It WORKED, wow!!!

I actually tried this approach earlier today by removing this:

WHERE 1 = 1

And then using this:

If @ename Is Not Null
Set @query = @query + ' WHERE (employeeName LIKE ''%'' + @ename + ''%'')'
Else
Set @query = @query

and I got zero records returned even though the value I used exists on the database table that I am using.

The difference is that I used double single quotes on either side of % like ''%'' + @ename + ''%'''

Thank you so very much.

Chikwe · May 14, 2025, 3:10am

I couldn't see where to accept your answer as the solution.

I will try again tomorrow to see if I see it.

Many thanks again.

harishgg1 · May 14, 2025, 12:48pm

hi

hope this helps

New SP

CREATE PROCEDURE [dbo].[UspGetPivotedDonors] @ename AS VARCHAR(75) = NULL
AS
  BEGIN
      DECLARE @cols  AS NVARCHAR(max), @query AS NVARCHAR(max), @searchTerm VARCHAR(75);

      SET @searchTerm = '%' + ISNULL(@ename, '') + '%'

      SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(Year(c.datecreated)) FROM   [dbo].[datedetails] c FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

      SET @query = N'SELECT employeeName,empTitle,email, ' + @cols + ' from (     select employeeName     ,empTitle     ,email     ,Year(dateCreated) as year     from SourceDetails      
	                 inner join Employees on SourceDetails.employeeID = Employees.EmployeeID     inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID  
					 where employeename like ''' + @searchterm + ''' ) x pivot (     max(year)      for year in (' + @cols + ') ) p'

      EXEC(@query)
  END;

mike01 · May 14, 2025, 3:26pm

Beware that this type of coding lends itself to SQL Injection

Chikwe · May 14, 2025, 6:58pm

@mike01 , can you come up with a better way to do this?

harishgg1 · May 14, 2025, 7:24pm

https://dba.stackexchange.com/questions/170990/preventing-sql-injection-in-dynamic-sql