Error code 1327 undeclared variable

Must declare the scalar variable error is a common occurrence for coders. There are several causes for this error. For instance, if the error precedes the term “<Variable Name>,” it might suggest a local variable is used in a stored procedure within a script. To learn more about the error and its solutions, read the full article.

Must Declare the Scalar Variable means that DECLARE statement wasn’t used to declare the function in a query. From the error stated above, there is a local variable within the function, script, or stored procedure that must be used with the Declare statement.

– Scalar Variable Declaration

The scalar variable declaration specifies the name of the variable’s data type and allocates its storage. Besides, the declaration can also assign an initial value and impose the NOT NULL constraint. So, the variable stores values with no internal components, and the value can change. In addition, to reference it, use the variable’s name.

Syntax:

What’s the Right Way to Declare a Variable in SQL?

To declare a variable in SQL, use the DECLARE statement. This function specifies some or all of the attributes of a name. In SQL, the Declare function initializes a variable by assigning it a name and a data type. A variable name must start with the @ sign.

The below example declares a variable @modelYear

DECLARE @modelYear SMALLINT;

The data type of the @modelYear variable is SMALLINT. When a variable is declared, its value is set to NULL by default. You can add an optional keyword AS between variable name and data type, as shown in the example below:

DECLARE @modelYear AS SMALLINT;

If you have several variables to declare, use commas to separate them, as shown below:

If you don’t declare a variable, you will receive the error: Must declare the scalar variable name.

– Scalar Variable Error SQL Example

A variable declaration will specify the name and data type of the variable. Still, a variable declaration will be an initial value for most data types.

The variable name should be a valid user-defined identifier. Here is an example on how you can declare it:

DECLARE
partNumber NUMBER(8);
partName VARCHAR2(30);
inStock BOOLEAN;
partPrice NUMBER(8,3);
partDescription VARCHAR2(40);
BEGIN
NULL;
END;
/

Also, you can declare a variable and constant with initial values as shown below:

DECLARE
hoursWorked INTG := 50;
employeeCount INTG := 0;
pi CONSTANT REAL := 3.14159;
radius REAL := 6;
area REAL := (pi * radius**6);
BEGIN
NULL;
END;
/

If you do not define an initial value for a variable, assign a value to it prior to using it in any other context.

You can impose the NOT NULL constraint on a scalar variable. This constraint prevents assigning a null value to the item.

The item can acquire this constraint either implicitly or explicitly.

Scalar Variable Warning Causes and Reasons

This error will occur in most cases if you try to use an undeclared variable. Let’s look at some scenarios that might lead to this error.

– Using an Undeclared Variable

If you do not declare a variable, you will receive this error message. The following example attempts to execute a statement without declaring it.

The error must declare the scalar variable @AuthorName pops out when executing the statement. The primary reason for the error is that the variable @AuthorName is used in the PRINT statement without declaring it. The SQL server does not allow this approach.

– Local Declared Variable After Go Statement

This scenario will also throw the error. Here is an example to illustrate every step that leads to the issue:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName
GO
PRINT @AuthorName

When you execute the statement, the output will be:

Must declare the scalar variable “@AuthorName.”

So, what’s wrong? In this example, the variable @AuthorName is used after the batch separator GO Statement in the PRINT statement—the scope of the local variables in the batch in which it is declared.

– Execute a Local Declared Variable in Dynamic SQL Statement

Dynamic SQL lets you create SQL statements dynamically at runtime. You can create more general-purpose and flexible statements, as the full text of the SQL statements may be unknown at compilation. However, errors can occur if not well executed. Check the example below:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE (‘PRINT @AuthorName’)

You will get the must declare scalar variable “@AuthorName” error when you run the code.

Typically, the variable @AuthorName is used in the statement executed by EXECUTE statement. EXECUTE statement does not have the visibility of the variables declared outside it. This results in the error.

The Best Fixes for Scalar Variable Failure

There are severe fixes available to solve the issue and get your code running smoothly. Check the solutions below to choose the one that is best for you.

– Solving Undeclared Variable

This is easy: declare the @AuthorName variable before using it in the PRINT statement as below:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName

Output:

In the example above, we use the DECLARE statement (a requirement in SQL server) to declare the variable. The second step specifies the name of the variable. Remember that all local names start with @ as it is a syntax necessity. Lastly, we have defined the data type of the variable.

Note that if we do not make any initial value assigned to a variable, it is initialized as a NULL.

– Go Statement or Batch Separator Fix

You will need to redeclare the @AuthorName variable before using it in the PRINT statement after the GO statement, as shown below.

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName
GO
DECLARE @AuthorName VARCHAR(100) = ‘Buscar’
PRINT @AuthorName

The output after executing the statement will be:

Vishal Balasubramanian
Buscar

Using the Go statement creates a new branch where the variables declared are not visible past the statement. So, all variables declared at the top of the file will not be accessible if the go statement is executed.

Although the variable @AuthorName is declared twice, the code still works. Here is why there is a batch separator, the Go statement, between the two variable declarations. The variable declared before the go statement is not accessed after the Go statement.

Typically, the SQL Server Management Studio ( SSMS) sends the first batch of statements to the SQL engine first for executions. It will send the second batch of statements after the Go statement to the SQL engine for execution when execution is over.

– Solving Local Declared Variable in Dynamic SQL Statement

Here is how to resolve the issue in the above statement:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE (‘PRINT ”’ + @AuthorName + ”” )

Output:

Alternatively, you can solve the above issue using the SP_EXECUTESQL statement, which allows parameterized statement. Check out this code for insights:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE SP_EXECUTESQL N’PRINT @AuthorName’,
N’@AuthorName VARCHAR(100)’,@AuthorName

The output after executing the program will be:

While executing a dynamic SQL, it is executed in a different scope. Therefore, if you declare your variables in the same scope as the VARCHAR variable with the SQL statement, you definitely will get that error.

But it is pretty impossible to access the variable from outside the dynamic SQL. You can resolve that issue in two ways:

• Insert the code that uses the variable in the dynamic SQL
• Insert the value in a temporary table (table will be seen outside the scope) and use it outside dynamic – execute SELECT against the temporary table.
• Parameterize and do not inject raw string in a dynamically generated statemen. SQL injection is a huge problem, rendering it an incorrect way of doing it.

– Other Causes and Fixes of Undeclared Variable

You might also get an “Undeclared variable” error when declaring a variable but not assigning it. Check out this example:

SELECT idSubscriber
INTO newsletterToSend
FROM subscribers

When you execute the statement, it generates an error: #1327 – Undeclared variable: newsletterToSend.

The problem, in this case, is that SQL does not support SELECT INTO [table] syntax. Instead, it only supports SELECT INTO [variable], and you can declare each variable individually and assign a value for every parameter in case of multiple SQL variables.

To solve this issue, you will need to use the CREATE TABLE syntax with SELECT, as shown below:

CREATE TABLE bar ([column list]) SELECT * FROM foo;

The below query will work fine:

CREATE TABLE New_Table_name
SELECT * FROM Original_Table_name;

SQL allows you to use two methods to assign values to a variable:

• SET statement
• SELECT statement

To use set, declare a variable and then assign a value using the SET statement.

For example:

DECLARE @AuthorName VARCHAR(100)
SET @TestVariable = ‘Vishal Balasubramanian’
PRINT @AuthorName

The output after executing the statement is:

To use the SELECT statement, insert it in the place of the SET statement.

You can use the SELECT statement to assign a value to a variable from the table, view, or scalar-valued functions. Nevertheless, if not done well, the error: Must declare the scalar variable in select statement can occur.

Must Declare the Scalar Variable Stored Procedure

In SQL, stored procedures let you create and store SQL queries and later execute them on a server. Their primary purpose is to hide direct SQL queries from the code and improve the performance of database operations such as selecting, updating, and deleting data.

While using them, you may encounter errors such as must declare the scalar variable @date. When this error occurs, you must declare and initialize this variable first.

Conclusion

While creating SQL queries, errors are inevitable, even if you are very careful. You need to reread your code and learn a few tricks for solving mistakes. Here is the rundown of our article:

• SQL requires that you declare statements using the DECLARE statement; you will get an error if you don’t.
• Always start local names with @ because it is a syntax necessity. So, you declare a variable as DECLARE @Name.
• A batch separator, the Go statement separates two statements ensuring that a variable declared before the go statement is not accessed after the Go statement.
• Dynamic SQL is executed in a different scope. Therefore, if you declare your variables in the same scope as the VARCHAR variable with the SQL statement, you definitely will get that error.

Since you know the possible causes and fixes, you can easily avoid the error or fix it soon as it pops up.

MariaDB 10.3.14

I have a function that generates the root id in a hierarchy:

The function is this:

CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ch INT;
DECLARE root INT;
SET ch = GivenID;

WHILE ch >= 0 DO
    SELECT id, parent_id INTO root, ch  FROM
    (SELECT id, parent_id FROM pctable WHERE id = ch) A;
END WHILE;
RETURN root;
END

I want to be able to provide the id, parent_id and table name as variables to this function, so i can easily be able to use it in different hierarchy tables easily.

something like this:

CREATE FUNCTION `getRootId`(GivenID INT,id_column_name varchar(45),parent_id_column_name varchar(45),target_table_name varchar(45))

One function to be used for both Topics, Indicators ….

I’m new to advanced SQL coding, so apologies if this is a newbie question.

Many thanks.

Updates:

I switched to procedure to use prepared statements:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getRootIdFlex`(IN GivenID INT,IN id_name varchar(45),IN parent_name varchar(45),IN tbl_name varchar(45) , OUT root varchar(1000))
DETERMINISTIC
BEGIN
DECLARE id_or_parent INT;

SET @tbl_name = tbl_name;
SET @id_name = id_name;
SET @parent_name = parent_name;

SET id_or_parent = GivenID;

SET @s := CONCAT('SELECT ?, ? INTO root, id_or_parent FROM (SELECT ?, ? FROM ? WHERE ? = id_or_parent) A');
PREPARE stmt FROM @s;
WHILE id_or_parent >= 0 DO
    EXECUTE stmt USING @id_name, @parent_name, @id_name, @parent_name, @tbl_name, @id_name;
END WHILE;
END

But when i execute the procedure this way:

SET @root := NULL;
CALL getRootIdFlex(11, 'Topic_id', 'Topic_Topic_id','Topic', @root);
SELECT @root;

I get:

Error Code: 1327. Undeclared variable: root

The root is declared as OUT variable. Why this error ?