Testing datetime dependent Stored Procedures
This week I was tasked with testing a stored procedure that was meant to output data on certain days. This was over a 120 day period, so I wanted to find some automated way of doing this, rather than changing the server date manually for each execution. The method I came up with involves the use of xp_cmdshell to execute the date command. Here’s an illustration of what I came up with.
First create this stored procedure. This will just check to see if it’s Saturday and output “Yes” if it is.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Rhys Campbell -- Create date: 2009-11-14 -- Description: Outputs "Yes" if it is Saturday -- otherwise "No" -- ============================================= CREATE PROCEDURE usp_isItSaturday AS BEGIN SET NOCOUNT ON; DECLARE @isItSaturday VARCHAR(3); SET @isItSaturday = CASE WHEN DATEPART(dw, GETDATE()) = 7 THEN 'Yes' ELSE 'No' END; SELECT GETDATE(), @isItSaturday; END GO
The script below will change the date by one day, then execute usp_isItSaturday, in sequence up to the end of 2009. I had to introduce the WAITFOR delay because SQL Server seems to take a few seconds to register the date change. The script will simply loop until the day changes before executing the store procedure.
-- Enable xp_cmdshell EXEC master.dbo.sp_configure 'xp_cmdshell', 1; RECONFIGURE; DECLARE @command VARCHAR(4000), @date VARCHAR(10), @starting DATETIME, @day INTEGER; SET @starting = GETDATE(); WHILE (@starting <= '2009-12-31T00:00:00') BEGIN SET @date = CAST(DATEPART(d, @starting) AS VARCHAR(2)) + '/' + CAST(DATEPART(m, @starting) AS VARCHAR(2)) + '/' + CAST(DATEPART(YYYY, @starting) AS VARCHAR(4)); -- Get the day for tracking when the date change has taken effect SET @day = DATEPART(d, @starting); SET @command = 'date ' + @date; EXEC xp_cmdshell @command, no_output; -- SQL Server takes a while to pickup the date change -- Loop around until the day has changed before executing the proc WHILE(@day <> DATEPART(d, GETDATE())) BEGIN WAITFOR DELAY '00:00:01'; END -- Call the procedure with the changed date EXEC dbo.usp_isItSaturday; -- Increment date by 1 day SET @starting = DATEADD(d, 1, @starting); END -- Disable xp_cmdshell EXEC master.dbo.sp_configure 'xp_cmdshell', 0; RECONFIGURE;
Here’s a sample of the output. You can see it has correctly identified the days that are Saturday.