CODEWITHSUNDEEP
sqlsql-servert-sqlvariables
Mutation Person
Mutation Person

Reputation: 30500

What are the consequences of declaring a SQL Server Variable with two (or more) '@' symbols?

Ordinarily, I would declare a variable

declare @ConType int;

Or something akin to this.

Recently in a code review I encountered a decalration using a double '@' (like the built in @@rowcount, for example), i.e.

declare @@ConType int;

I notice that one can stupulate any (reasonable) number of '@':

declare @@@@ConType int;

And the variable should function ok. So, the following should work:

declare @@@@ConType int;
set @@@@ConType = 1;
select @@@@ConType;

Obvoiusly, the above is a little stupid, but my question is really whether there is any significance in declaring variables in this way? Are there any side-effects? Should we avoid doing so?

Upvotes: 11

Views: 4617

Answers (4)

Robb
Robb

Reputation: 3851

As far as I'm concerned there is nothing overly wrong with it as long as your not colliding with a system function, however just because @@ is a valid variable name doesn't mean you should use it.

Chances are if your declaring variables @var, @@var and @@@var then your variables are badly named and its going to cause confusion to those who have to maintain it in the future.

Edit -

For further reading on the subject of just how much you can abuse sql while keeping it valid see this wonderful post by Phil Factor - Evil Code

Upvotes: 3

schellack
schellack

Reputation: 10274

From MSDN:

The names of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of Microsoft SQL Server, the @@functions are referred to as global variables, they are not variables and do not have the same behaviors as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.

[EDIT] To clarify, SQL Server will let you put however many at signs you want at the front of your variable names, but technically @@ objects are not variables at all. They behave like that though they look like system functions. Some people try to use the @@ to designate a global variable, but that doesn't work; there is no way for you to create a global variable. This behavior is typically just left over from using earlier versions of SQL Server.

Upvotes: 4

Martin B
Martin B

Reputation: 24140

The first character of a variable name has to be an at sign ('@'). Any at signs after that do not have any particular significance and are treated just like any other character.

However, you should avoid declaring variables that begin with a double at sign ('@@') because, in the words of MSDN:

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

Of course, this means that variable names beginning with three or more at signs should also not be used.

I guess it's not exactly wrong to use at signs later in the variable name if the second character is not an at sign, but it just looks confusing, so it's probably not a good idea either.

Upvotes: 17

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

It looks silly, and Microsoft recommends against it in the rules for identifiers, ("Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@."), but otherwise the examples you've given comply with those same rules and are perfectly valid.

There should be no harm in using variables of this form, although there's probably no legitimate reason for doing it either.

Upvotes: 2

Related Questions