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