Must declare scalar variable in a unit test

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.

Answer

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.