Must declare the scalar variable: How to Rid Your Code of this Pesky Error

Why is SQL giving you shade for not declaring variables?

Variables need a formal introduction (DECLARE) before being put to work (SET, SELECT). If you skip the introduction, SQL Server gives you the 'cold shoulder'. Also, be wary of using GO; this guy plays the part of the party pooper and ends your variables' scope. This means any variable declared before GO can't hang out afterwards:

DECLARE @Hangover INT = 1;
GO
SELECT @Hangover;  -- Throws error (and maybe a fit)


When dealing with dynamic SQL, concatenate at your own peril; It leaves the needle-thin crack for SQL injection attacks. Instead, sp_executesql comes to the rescue with parametrized queries:

DECLARE @UserID INT = 1337;    /* No, not the leet hax0r! */
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Users WHERE UserID = @UserID';

EXEC sp_executesql @SQL, N'@UserID INT', @UserID; /* Safety first, hacking SQL-ers get outrun */


Concatenating Strings and Integers: How to glue stuff together without mess?

If you prefer not to fall into a null void when concatenating strings, the CONCAT() function is your gravity-defying tool:

DECLARE @name VARCHAR(50) = 'John', @surname VARCHAR(50);  /* Mystery surname */
SELECT CONCAT(@name, ' ', @surname);  /* Prints 'John ', no NULL nightmares */


On occasions where you just can't prevent mixing a string with a non-string partner like an integer, string it along using CONVERT() or CONCAT():

DECLARE @Integer INT = 123;  /* Just a random number, not an IQ score */
DECLARE @String VARCHAR(255) = 'Item Number: ';  /* It's just an ID, don't flatter yourself */

/* Walk them down the aisle with CONVERT */
SELECT @String + CONVERT(VARCHAR(10), @Integer);

/* Or, arrange a rendezvous with CONCAT */
SELECT CONCAT(@String, @Integer);


For better security over spontaneity, choose parameterization over direct concatenation:

/* The not-so-sketchy-way */
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Users WHERE UserID = @UserID';
EXEC sp_executesql @SQL, N'@UserID INT', @UserID;

/* The string concatenation way: don't be this guy */
DECLARE @TableName NVARCHAR(128) = N'Users';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + @TableName; 
EXEC (@SQL);


Doing all these will make your code not only resistant to errors but robust and secure too. Let's put these in pictures!

SQL Scenarios Simplified

Dynamic SQL: The Uncharted Territory

Dynamic SQL is that part of SQL that doesn't play by parent SQL Server's rule. When using dynamic SQL, you'd need to declare and set your variables within the string:

-- Example of dynamic SQL
EXEC sp_executesql N'DECLARE @Counter INT; SET @Counter = 5; SELECT @Counter;';  


Failing to do so would send you the infamous "Must declare the scalar variable" gift.

GO: The Party Pooper

The GO command resets all rules — and along with it, any variable declared before it. However, you can have your cake and eat it:

-- Cake: declare & use variables before 'GO'
DECLARE @Cake VARCHAR(50) = 'Yummy cake';
PRINT @Cake;
GO

-- Unfortunately, the cake is no longer available
PRINT @Cake;  -- Oops, we get an error!


To prevent this grievance, don't call GO between declaring and using your variable. After all, it's your cake!

Careful with the Order!

SQL variables are creatures of habit. They like to be declared, initialized, and then finally used. Any switch in this order and they protest:

-- This is a strike!
SET @ActiveUser = 'admin'; -- Error!
DECLARE @ActiveUser VARCHAR(50);

-- A peaceful day
DECLARE @ActiveUser VARCHAR(50);
SET @ActiveUser = 'admin';


If you stick to the right order, you can avoid SQL grumbling about undeclared scalar variables.