Comments
Sort by recent activity
The closest you can get is faking any underlying tables which should at least reduce some of the setup requirements.
The only other alternative would be to code up an ALTER FUNCTION statement using dynamic SQL that just returns the data you want it to. This would get rolled back along with any other changes when the test completes.
I'm thinking something like this: DECLARE @sql varchar(MAX);
SET @sql = 'ALTER FUNCTION [dbo].[TVF_PROMsGetUploadedScorecardsFilteredByHeaders]
(
@PipedHeaderIDs varchar(50)
)
RETURNS @tblVariable table
(
UploadedID int
)
AS
BEGIN
INSERT @tblVariable
(
UploadedID
)
SELECT 99
UNION SELECT 52
UNION SELECT 51
RETURN
END'
BEGIN TRAN
EXEC (@sql)
SELECT * FROM TVF_PROMsGetUploadedScorecardsFilteredByHeaders('')
ROLLBACK TRAN
/ comments
The closest you can get is faking any underlying tables which should at least reduce some of the setup requirements.
The only other alternative would be to code up an ALTER FUNCTION statement using...
You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.
As with other users, specifying the schema correctly in all references is a common typo/gotcha.
Are you running against a case-sensitive instance/database? There were a couple of historic bugs around casing for the Setup procedure name - fixed in different versions.
Otherwise, try posting your code and we'll see if we can help / comments
You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.
As with other users, specifying the s...
Simon, just a thought - if you're having issues with FakeTable it might be worth starting a new thread.
FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce some interesting questions around how we do certain types of test. / comments
Simon, just a thought - if you're having issues with FakeTable it might be worth starting a new thread.
FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce so...
Simon,
Ok so we know that [SetUp] is being called. And, if you've corrected the schema references, we need to find out whether the table is actually created and if it is, at point it disappears again.
Try adding this to the end of your SetUp procedure:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('SetUp: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE IF OBJECT_ID(N'dbo.Actual') > 0
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema but deos exist in dbo schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then add this at the top of the test procedure IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('In Test: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('In Test: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
and then add this just before you try to insert the Actual table:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('Before Assert: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('Before Assert: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then just run the one test with tSQLt.Run and see what the output is.
Also, you were correct in naming the set-up procedure [SetUp]. tSQLt looks for this before running each test. If you'd left it as [test SetUp], it would have got called once because it was treated as just a regular test.
Also, in case it wasn't clear earlier, the reason why I recommend putting the [actual] and [expected] tables in the test schema rather than dbo is because one day you might actually be testing a database that has a table named like that. / comments
Simon,
Ok so we know that [SetUp] is being called. And, if you've corrected the schema references, we need to find out whether the table is actually created and if it is, at point it disappears aga...
Simon,
I think I've just spotted your issue. It's difficult to see on the web page without sysntax highlighting and I've only just seen it on a another review of the code.
When you call AssertEqualsTable, if your expected and actual tables are in a schema other than dbo (which they are), you have to specify the schema name too. EXEC tSQLt.AssertEquals @Expected = 'BoardSummary.Expected', @Actual = 'BoardSummary.Actual'
/ comments
Simon,
I think I've just spotted your issue. It's difficult to see on the web page without sysntax highlighting and I've only just seen it on a another review of the code.
When you call AssertEqua...
Simon,
I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.
NB: Odd, I didn't see the refactored code when I first looked
Looking at the example code you posted, I'm assuming that this is before refactoring creation of the actual and expected table out into the setup method. The most obvious thing to check is that you also remove the DROP TABLE statements at the beginning of [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]. In fact arguably the DROPS aren't really required since the CREATES get rolled back at the end of each test. It's my OCD that makes me do it in my code :-)
If that's not the problem, try creating a [BoardSummary].[SetUp] that just does RAISERROR('Setup called!', 0, 1) WITH NOWAIT; then run just one test tSQL.Run '[BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]' in another window to see if SetUp does actually get called. Then build things up from there.
Another thing to check (although a longshot) if you happen to be running on a case-sensitive database is that you are running the latest version of the tSQLt framework SELECT * FROM tSQLt.Info() should yield 1.0.4504.21220. There was a small issue in an earlier version WRT case-sensitivity of the SetUp call. Mind you if you've installed it via SQL Test this shouldn't be a problem.
If these suggestions don't work, let me know and I'll see what else I can come up with / comments
Simon,
I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.
NB: Odd, I didn't see the refactored code when I first looked
L...
Just spotted something...
I notice that your creating the actual and expected tables in the [dbo] schema. It is a better idea to create these tables in the same schema as the test suite.
But you are refencing them as if they are in the [BoardSummary] schema in your test:
INSERT INTO [BoardSummary].Expected
VALUES ( 90, 80, 80, 60, 60, 35 )
INSERT INTO [BoardSummary].Actual
( GreenUpper ,
GreenLower ,
AmberUpper ,
AmberLower ,
RedUpper ,
RedLower )
SELECT GreenUpper ,
GreenLower ,
AmberUpper ,
AmberLower ,
RedUpper ,
RedLower
FROM @Exec AS E
WHERE GroupedBy = 'TEST' / comments
Just spotted something...
I notice that your creating the actual and expected tables in the [dbo] schema. It is a better idea to create these tables in the same schema as the test suite.
But you a...
Yes this does still exist.
I blogged about it a while back Unit Testing Databases with tSQLt Part 6 – testing a SELECT stored procedure under the heading "Automatic Setup".
If that doesn't help, perhaps you could post your code. / comments
Yes this does still exist.
I blogged about it a while back Unit Testing Databases with tSQLt Part 6 – testing a SELECT stored procedure under the heading "Automatic Setup".
If that doesn't help, ...
An alternative view might be that if one uses cursors so rarely as to need a snippit to remeber all the correct syntax that's a good thing. / comments
An alternative view might be that if one uses cursors so rarely as to need a snippit to remeber all the correct syntax that's a good thing.