the meaning of @ sign in SQL statement

I have seen in some SQL statements they use the @sign in front of variables, I don't know what this means.

example :
Update vendors
set vendorname=@vendorename where vendorID=@@identity

in the statement above you can notice the usage of the @ sign befor a variable, and @@ before the identity word.

any explanations?

thanks
jskfanAsked:
Who is Participating?
 
VeryNiceManCommented:
hi;

DECLARE x int
SET x =5

YES; will throw an error as SQL will think this is a CURSOR rather that a local variable.

hope that helps
best regards
0
 
Clever_BobCommented:
Its simply the syntax for referencing or declaring a variable in a stored procedure or SQL statement, you can just treat them as variables.

Hope this helps.
0
 
Clever_BobCommented:
and there is heaps more here

http://msdn2.microsoft.com/en-US/library/ms187953.aspx

e.g.

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
VeryNiceManCommented:
hi;

(@@) means internal to SQL Server; there are many of those ready server functions like:
@@ERROR
@@IDENTITY
@@OPTIONS
@@VERSION
@@LOCK_TIMEOUT
etc....


(@) on the other hand helps in 2-scenarios:
First: Variables Assignment; for example in Stored Procedures OR User-Defined-Functions you can do the following:

DECLARE @x int
SET @x = 5

OR...

DECLARE @mName varchar(50)
SET @mName = 'Bill Gates'

OR...
DECLARE @mAge int
SET @mAge = (SELECT Age FROM Master WHERE City='London')


Second: as you said it; in a Procedure or Function variables; for example:

CREATE PROCEDURE UpdateMyBase
(
@mAge int,
@mName varchar(100)
)

OR...

CREATE FUNCTION Calculate_Area
RETURNS Decimal
(
@x int,
@y int
)


hope this help
best regards;
0
 
jskfanAuthor Commented:
so it's just a convention.

if I declare a variable:

DECLARE x int
SET x = 5

would it throw an error?


0
 
t_itaniumCommented:
..the idea for @ before the variable...is :

sql consider this variable as a parameter.. so you can pass from your code what ever you want to this parameter..

example:
if you use this statement

Update vendors
set vendorname=@vendorename

you can pass a value in your code to the parameter @vendoename..so the query will be complete..
this query will not be executed unless provided with @vendorname parameter

cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.