Must declare scalar variable in a unit test

Question

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.

Looks like a misprint. Try copy-paste `@ArbitaryNumberOfCharacters` from `DECLARE` to other parts of your code. — Serg, Mar 31 '20 at 15:10

score 0 · Accepted Answer · answered Mar 31 '20 at 15:23

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.

Must declare scalar variable in a unit test

1 Answers1