I have the following table:
CREATE TABLE [dbo].[AttendanceRecords]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[SampleDate] [date] NULL,
[Weekday] [nvarchar](20) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Department] [nvarchar](50) NULL,
[Area] [nvarchar](20) NULL,
[TotalTime] [int] NULL,
[FirstInTime] [datetime] NULL,
[LastOutTime] [datetime] NULL,
[GrossTotalTime] [int] NULL
) ON [PRIMARY]
and the following stored procedure in SQL Server 2019:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetGeneralAttendanceData]
(
@FirstName nvarchar(50) = null,
@LastName nvarchar(50) = null,
@StartDate datetime = null,
@EndDate datetime = null,
@Department nvarchar(50) = null,
@PartialName nvarchar(50) = null
)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #finalTable (FirstName NVARCHAR(50), LastName NVARCHAR(50));
CREATE TABLE #secondaryTable (FirstName NVARCHAR(50), LastName NVARCHAR(50));
SELECT *
INTO #TimeCards
FROM AttendanceRecords
WHERE 1=1
AND (@FirstName is NULL or FirstName = @FirstName)
AND (@LastName is NULL or LastName = @LastName)
AND (@StartDate is NULL or SampleDate >= @StartDate)
AND (@EndDate is NULL or SampleDate <= @EndDate)
AND (@Department is NULL or Department = @Department)
AND (@PartialName IS NULL OR FirstName LIKE '%' + @PartialName + '%' OR LastName LIKE '%' + @PartialName + '%')
ORDER BY SampleDate;
DECLARE @cols NVARCHAR(MAX) = null;
DECLARE @colsWithoutWeekdays NVARCHAR(MAX) = null;
DECLARE @colsAndTypes NVARCHAR(MAX) = null;
DECLARE @query NVARCHAR(MAX) = null;
DECLARE @SQL NVARCHAR(MAX) = null;
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(d.SampleDate)
FROM
(
SELECT convert(nvarchar(25), SampleDate,25) as SampleDate
FROM #TimeCards
) d
GROUP BY SampleDate
ORDER BY SampleDate
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SELECT @colsAndTypes = STUFF((SELECT ',' + QUOTENAME(d.SampleDate) + ' NVARCHAR(50)'
FROM
(
SELECT convert(nvarchar(25), SampleDate,25)+' - '+ [WeekDay] as SampleDate
FROM #TimeCards
) d
GROUP BY SampleDate
ORDER BY SampleDate
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @SQL = 'ALTER TABLE #finalTable ADD ' + @colsAndTypes;
EXECUTE(@SQL);
SET @SQL = 'ALTER TABLE #secondaryTable ADD ' + @colsWithoutWeekdays;
EXECUTE(@SQL);
SET @query = 'SELECT FirstName, LastName, ' + @cols + '
FROM
(SELECT FirstName, LastName, SampleDate, TotalTime FROM #TimeCards) x
pivot
(sum(TotalTime) for SampleDate in (' + @cols + ')) p
order by LastName, FirstName';
SET @SQL = 'INSERT INTO #finalTable ' + @query;
EXECUTE(@SQL);
SET @SQL = 'SELECT * FROM #finalTable';
EXECUTE(@SQL);
SET @SQL = 'DROP TABLE #finalTable';
EXECUTE(@SQL);
SET @SQL = 'DROP TABLE #secondaryTable';
EXECUTE(@SQL);
SET @SQL = 'DROP TABLE #TimeCards';
EXECUTE(@SQL);
IF OBJECT_ID('tempdb..#finalTable') IS NOT NULL DROP TABLE #finalTable;
IF OBJECT_ID('tempdb..#secondaryTable') IS NOT NULL DROP TABLE #secondaryTable;
IF OBJECT_ID('tempdb..#TimeCards') IS NOT NULL DROP TABLE #TimeCards;
END
I've got to be missing something because it appears as though the data keeps staying resident between executions. I found that since I'm altering the temp tables using the Execute command (can you tell me what this is called, transaction?) so I had to do the same to drop the temp table. This took me a while to figure out because I thought all temp tables were stored in the same place. I really don't know where these are though, I searched and cannot find them.
My problem now is that when I search based upon @StartDate and @EndDate, the results will shrink, but will not expand after enlarging the gap between the two dates.
Plus, when I reduce the gap, I get the error:
Msg 0, Level 11, State 0, Line 2
A severe error occurred on the current command. The results, if any, should be discarded.
But I just press F5 again and it executes correctly.
So, for example, I'll do the following:
DECLARE @return_value int
EXEC @return_value = [dbo].[GetGeneralAttendanceData]
@StartDate = N'08/26/2024',
@EndDate = N'09/29/2024'
SELECT 'Return Value' = @return_value
GO
This will work with no issue when ran just after the stored procedure has been altered.
Then I'll change @EndDate to 08/29/2024 and at first I get the error, then I'll run it a second time and it works correctly.
Finally, I will change the date back to 09/29/2024 and it will continue to return the results from the 08/29/2024 query. I've worked on this for a few hours now and I cannot find where I've gone wrong.
Please help and thank you in advance.
I don't know what your issue was with the temp tables, but they should drop automatically when you close the connection. Best practice would be to DROP IF EXISTS before you start.
But you don't actually need temp tables here. You can create dynamic SQL with the base query, then use that to first get the columns, then create the pivot. It will also be more efficient than using IS NULL OR conditions.
CREATE OR ALTER PROCEDURE [dbo].[GetGeneralAttendanceData]
(
@FirstName nvarchar(50) = null,
@LastName nvarchar(50) = null,
@StartDate datetime = null,
@EndDate datetime = null,
@Department nvarchar(50) = null,
@PartialName nvarchar(50) = null
)
AS
SET NOCOUNT ON;
DECLARE @baseQuery nvarchar(max) = '
FROM AttendanceRecords ac
WHERE 1=1
';
IF @FirstName IS NOT NULL
SET @baseQuery += '
AND FirstName = @FirstName';
IF @LastName IS NOT NULL
SET @baseQuery += '
AND LastName = @LastName';
IF @StartDate IS NOT NULL
SET @baseQuery += '
AND SampleDate >= @StartDate';
IF @EndDate IS NOT NULL
SET @baseQuery += '
AND SampleDate <= @EndDate';
IF @Department IS NOT NULL
SET @baseQuery += '
AND Department = @Department';
IF @PartialName IS NOT NULL
SET @baseQuery += '
AND (FirstName LIKE ''%'' + @PartialName + ''%'' OR LastName LIKE ''%'' + @PartialName + ''%''';
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX) = '
SELECT @cols = STRING_AGG(QUOTENAME(d.SampleDate), '','') WITHIN GROUP (ORDER BY SampleDate)
FROM
(
SELECT convert(nvarchar(25), SampleDate,25) as SampleDate
' + @baseQuery + '
GROUP BY SampleDate
) d;
';
PRINT @sql; -- your friend
EXEC sp_executesql @sql,
N'@cols nvarchar(max) OUT,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@StartDate datetime,
@EndDate datetime,
@Department nvarchar(50),
@PartialName nvarchar(50)',
@cols OUT,
@FirstName,
@LastName,
@StartDate,
@EndDate,
@Department,
@PartialName;
SET @sql = '
SELECT FirstName, LastName, ' + @cols + '
FROM (
SELECT FirstName, LastName, TotalTime,
convert(nvarchar(25), SampleDate,25) as SampleDate
' + @baseQuery + '
) x
pivot (
sum(TotalTime) for SampleDate in (
' + @cols + '
)
) p
order by LastName, FirstName;
';
PRINT @sql; -- your friend
EXEC sp_executesql @sql,
N'@FirstName nvarchar(50),
@LastName nvarchar(50),
@StartDate datetime,
@EndDate datetime,
@Department nvarchar(50),
@PartialName nvarchar(50)',
@FirstName,
@LastName,
@StartDate,
@EndDate,
@Department,
@PartialName;
To be honest I would strongly recommend you use your application to do dynamic pivots, as SQL is not really designed for this.