1

I have a stored procedure that uses getdate() for taking a current date and compare with provided.

But a problem with testing arose -> usually for testing we have prepared input and output data, and predefined dates among this data. But getdate() will always return current time, so test results will differ in time.

I think there is no way to change what getdate() will return? But take getdate() out of the stored procedure and use some input parameter instead also is not an option.

What are the best practices to test such stored procedure, to make the test's result constant?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nahab
  • 1,308
  • 17
  • 38
  • 4
    You could create a custom function which calls `get-date` and returns the output; then when you're testing you could override the definition of that function to return your test value? – JohnLBevan Feb 27 '17 at 15:17
  • 2
    Other than John's suggestion, I think the only other way is to pass the date into the proc. – DavidG Feb 27 '17 at 15:18
  • 1
    Could you clarify how you are unit testing a stored procedure? Usually that is the realm of integration testing. Are you using a SQL-based framework like TSQLUnit, Microsoft's SQL Server Data Tools, or something else? – TrueWill Feb 27 '17 at 15:19
  • 1
    @TrueWill I uses jUnit; about type of testing - it can be called integration testing or whatever, but actual question about getdate() – nahab Feb 27 '17 at 15:23

1 Answers1

0

Looks like it can't be done short of changing the server date or modifying the stored procedure:

Changing the output of Getdate

Under these constraints, you can check if the procedure returns a result set without errors. If the procedure queries other tables, you could populate those tables with data based on the current date, call the procedure, validate the results, then remove the data from the other tables.

Alternately, if the procedure is truly fixed and unchanging, the cost of automated testing may be higher than the value of it in this (rare) case. You could validate the procedure manually (QA).

Community
  • 1
  • 1
TrueWill
  • 25,132
  • 10
  • 101
  • 150
  • Yeah, hopping for better approach. For my case substitute function as mentioned by @JohnLBevan and in link provided by you will be the best, as don't want to introduce date arithmetic in tests and be based on really current date, because of not constant day of weeks in this case :( – nahab Feb 27 '17 at 17:12