Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Get a List of User Views Within a Database
Error Messages
Home > SQL Server Error Messages > Msg 134 - The variable name '<Variable Name>' has already been declared. Variable names must be unique within a query batch or stored procedure.
SQL Server Error Messages - Msg 134 - The variable name '<Variable Name>' has already been declared. Variable names must be unique within a query batch or stored procedure.

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
Related Articles :