I have a stored procedure that uses the GETUTCDATE() function several times. It's a very specific line of business sproc, so it probably wouldn't make a lot of sense to show it here. Having said that, it might be helpful to know the sproc will only ever be called for the current year. Here's a contrived example that doesn't show the complexity of what I'm doing, but should help illustrate what I'm talking about:
CREATE PROCEDURE dbo.GenerateRequestListForCurrentYear AS BEGIN
SELECT RequestId, StartDate, EndDate FROM Requests
WHERE YEAR(EndDate) = YEAR(GETUTCDATE());
END;
My test looks like this:
CREATE PROCEDURE testClass.[test Requests are generated for the current year] AS BEGIN
-- arrange
EXEC tSQLt.FakeTable 'dbo.Requests';
INSERT INTO dbo.Requests (RequestId, StartDate, EndDate) VALUES
(1, '2/1/14', '2/10/14'), (2, '2/1/13', '2/10/13');
SELECT TOP (0) * INTO #Expected FROM dbo.Requests;
SELECT TOP (0) * INTO #Actual FROM dbo.Requests;
INSERT INTO #Expected VALUES
(1, '2/1/14', '2/10/14');
-- act
INSERT INTO #Actual
EXEC dbo.GenerateRequestListForCurrentYear;
-- assert
EXEC tSQLt.AssertEqualsTable #Expected, #Actual;
END;
I see a couple options:
Both of these seem like options that are only necessary for the test which seems a little smelly; there wouldn't ever be a need to parameterize this sproc with regard to how it's being called from the primary application.
As it relates to dependencies with built-in functions in SQL that have their own dependencies, is there a way to fake those calls in a tSQLt test? Is there a better way to fake the call to GETUTCDATE() to return a date I specify in my tests using tSQLt or are these my only options?
You can use either approach, and like Andrew, I have used both in the past.
I generally prefer option 1 (passing in the parameter) whenever I can however. It has the added benefit of making an execution more repeatable. In other words, I can pass in an arbitrary date and that is often useful for support and has on more than one occasion made adding a new feature easier. I find this useful in languages outside of SQL also. It has the effect of making the result of the individual, complex piece of code not dependent on when it was executed, but instead on the parameters received and the data in the system. In other words, there is a benefit beyond testing of passing in the current time as a parameter and may ease your hesitation. (It should be noted that I also believe that there is nothing wrong with making a design decision to support testing - it is an important part of the product).
Option 2 also works, and you would just simply create your own function that wraps the system function. You should be aware however, that this may have implications on performance and you should test it thoroughly. SQL Server is not always kind to you when functions are involved in queries. I don't like the advice to never use scalar functions in queries due to supposed performance problems, because sometimes there is not a performance penalty and sometimes the difference in performance is a non-issue. However, you should be aware of the potential if you decide to wrap the function.