?
Solved

the meaning of @ sign in SQL statement

Posted on 2006-05-25
6
Medium Priority
?
417 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:jskfan
6 Comments
 
LVL 7

Assisted Solution

by:Clever_Bob
Clever_Bob earned 400 total points
ID: 16765700
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
 
LVL 7

Expert Comment

by:Clever_Bob
ID: 16765708
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
 
LVL 2

Assisted Solution

by:VeryNiceMan
VeryNiceMan earned 1200 total points
ID: 16765826
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jskfan
ID: 16768720
so it's just a convention.

if I declare a variable:

DECLARE x int
SET x = 5

would it throw an error?


0
 
LVL 5

Assisted Solution

by:t_itanium
t_itanium earned 400 total points
ID: 16769259
..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
 
LVL 2

Accepted Solution

by:
VeryNiceMan earned 1200 total points
ID: 16769620
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Six Sigma Control Plans

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question