I am trying to design a unit test that ensures that a message is bigger than a certain number of characters. My code is:
EXEC tSQLt.NewTestClass 'TestMarketingEnoughRows';
GO
CREATE OR ALTER PROCEDURE TestMarketing.[test that API_GetStandardDisclaimerText tests to make sure that the message is long enough]
AS
BEGIN
DROP TABLE IF EXISTS #Actual;
CREATE TABLE #Actual
(
Note NVARCHAR(MAX),
NoteWithHTML XML
);
INSERT #Actual
(
Note,
NoteWithHTML
)
EXEC Marketing.API_GetStandardDisclaimerText @Username = 'AnyValue', -- varchar(100)
@OneParagraphPerRow = 0; -- bit
SELECT LEN(Note),
LEN(CAST(NoteWithHTML AS NVARCHAR(MAX)))
FROM #Actual;
DECLARE @ArbitaryNumberOfCharacters INT = 15000;
DECLARE @ThisShouldEqualOne BIT =
(
SELECT CASE
WHEN LEN(Note) > @ArbitraryNumberOfCharacters
AND LEN(CAST(NoteWithHTML AS NVARCHAR(MAX))) > @ArbitraryNumberOfCharacters THEN
1
ELSE
0
END
FROM #Actual
);
EXEC tSQLt.AssertEquals @Expected = @ThisShouldEqualOne, -- sql_variant
@Actual = @ArbitaryNumberOfCharacters, -- sql_variant
@Message = N'test mctestyface'; -- nvarchar(max)
END;
GO
EXEC tSQLt.Run 'TestMarketingEnoughRows';
I am getting the following errors:
Msg 137, Level 15, State 2, Procedure test that API_GetStandardDisclaimerText tests to make sure that the message is long enough, Line 25 [Batch Start Line 3] Must declare the scalar variable "@ArbitraryNumberOfCharacters". Msg 137, Level 15, State 2, Procedure test that API_GetStandardDisclaimerText tests to make sure that the message is long enough, Line 33 [Batch Start Line 3] Must declare the scalar variable "@ThisShouldEqualOne".
I'm at a loss because I have declared the variables and I don't appear to be giving a type error? The other posts on stackoverflow on this topic didn't seem to help me.
So, there's a lot wrong with this code. I suggest you look at the spelling of your variable. There are at least 2 different spellings in your code.
Then, the assertequals comparison doesn't make sense. Shouldn't you compare @ThisShouldEqualOne to 1 instead of to the count?
But, I recommend using tSQLt.Fail instead of setting a flag and comparing that to one.
Finally, What is the purpose of requiring 15K characters? I would rather go for testing that a specific part is actually part of the note. If this test fails, it cannot really tell you anything about what went wrong. Instead, I recommend that you write test like [test that NoteWithHTML actually contains HTML].
To that point, you should separate testing the two values into two tests. Each test should have 1 reason to fail. (If you aim for that you'll significantly reduce your test maintenance costs.)
P.S.: It is a bad practice to drop a temp table at the beginning of a stored procedure. The CREATE TABLE #Actual will never fail. Even if there should be another table with that name in existence already. But it can have all kinds of performance implications if you drop it.