|
|
Error Message
Server: Msg 134, Level 15, State 1, Line 1
The variable name '<Variable Name>' has already been
declared. Variable names must be unique within a query
batch or stored procedure.
Causes
As the error message suggests, variables names must be unique within a query batch (or batch of Transact-SQL statements) or stored procedure. Although not mentioned in the error message, variable names must also be unique within user-defined functions and triggers. Function and stored procedure parameters are also considered variables and therefore included in the checking for the uniqueness of variable names.
|
The simplest way to generate this error is as follows:
DECLARE @FirstName VARCHAR(100)
DECLARE @firstName VARCHAR(50)
Msg 134, Level 15, State 1, Line 2
The variable name '@firstName' has already been declared.
Variable names must be unique within a query batch or stored procedure.
Unlike some programming languages such as C#, Transact-SQL statements are not case-sensitive and therefore variable names are not also case-sensitive. Variable @FirstName is considered the same as variable @firstName even though the first character has different character casing and even though each variable is defined with different data types.
Here’s another example on how the error can be encountered within a user-defined function:
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @Date DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @Date DATETIME
SET @Date = DATEADD(DD, 0, DATEDIFF(DD, 0, @Date))
RETURN @Date
END
GO
Msg 134, Level 15, State 1, Procedure ufn_GetDateOnly, Line 7
The variable name '@Date' has already been declared.
Variable names must be unique within a query batch or stored procedure.
This user-defined function returns just the date part of a given DATETIME value. It tries to declare a local variable that will hold the return value of the function. But since the local variable declared within the function is the same as the name of the parameter, an error is generated.
Solution / Work Around:
To avoid this error message, make sure that any variable is unique within a query batch, function, stored procedure or trigger, taking into consideration the parameters for the case of functions and stored procedures.
For the case of the user-defined function earlier, the error can be avoided by using a different variable name for the output date, as follows:
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @Date DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @OutputDate DATETIME
SET @OutputDate = DATEADD(DD, 0, DATEDIFF(DD, 0, @Date))
RETURN @OutputDate
END
The sample user-defined function here is just used to demonstrate how the error can be encountered and avoided. In reality, there’s no need to create a separate local variable for the output of the user-defined function. This user-defined function can be rewritten as follows, which does not make use of a local variable.
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @Date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(DD, 0, DATEDIFF(DD, 0, @Date))
END
GO
|