Unlike programming languages, SQL doesn’t allow variable to be assigned a value using the following syntax ( instead we must use SET or SELECT ):
@i=100; //error
Is there particular reason why SQL doesn’t allow this?
thank you
views:
148answers:
8
+9
A:
Why does any language use the syntax it uses?
To maintain conventions, prevent ambiguity, and simplify parsing.
Notice that the syntax of SET statements (properly called assignment statements) is similar to the syntax in the SET clause of an UPDATE statement:
UPDATE mytable SET somecolumn = 100;
Bill Karwin
2010-07-14 19:27:05
+2
A:
Well, SQL isn't a programming language.
I can't say that I know the reason. However, it likely has something to do with the intended use of SQL. Namely, working with data. Who works with a lot of data? Business people. The language, as such, uses more words and fewer symbols. Compare the style of VisualBasic to C++.
George Marian
2010-07-14 19:27:34
"SQL isn't a progamming language `[citation needed]` " What exactly makes one language a programming language and another language not a programming language?
Bill Karwin
2010-07-14 19:30:28
SQL is respected as a 4G programming language; Java/C#/etc are considered 3G. SQL has a higher rating due to closer to natural English syntax, among a few other things I forgot from school...
OMG Ponies
2010-07-14 19:34:42
@Bill Karwin That depends on what your definition of the 'is' is... ;) SQL is two parts, a Data Manipulation Language and a Data Definition Language. It depends on how you want to split the hair. :)
George Marian
2010-07-14 19:35:12
@OMG Ponies One problem with the whole 3G/4G thing is that the G stand for Generation. So, bolting on top another meaning to 4G I.e. not the 4th iteration of language types, but rather calling it a 'rating' is problematic.
George Marian
2010-07-14 19:37:50
@George Marian: Doesn't change the fact 3G/4G classifies programming languages ;)
OMG Ponies
2010-07-14 19:45:04
@OMG Ponies Well, we're not the first people to have this debate. I doubt we'll be the last. :) Also, was SQL considered a 4G language when it was created?
George Marian
2010-07-14 19:47:28
4GL is a term for a language that is specialized to a domain of work, and provides greater abstraction for programmers. A 4GL is "further from the machine" and closer to the domain. *Domain-specific language*, or DSL, is a more recent term for a similar idea. The term 4GL didn't catch on until 1982, but SQL and even some earlier languages do meet its definition.
Bill Karwin
2010-07-14 20:28:13
+4
A:
You may as well say why can't I do this in c#?
SELECT * FROM MyArray[]
Why must I iterate and mess around with Console.WriteLine? It's just a set collection of data that I want to see in one go.
gbn
2010-07-14 19:30:24
+2
A:
After a variable is declared, it is initialized to NULL. Use the SET statement to assign a value that is not NULL to a declared variable.
So says TSQL SET documentation. I don't remember having to use the SET keyword in Oracle's PLSQL.
OMG Ponies
2010-07-14 19:30:30
Add that MySQL also only uses the SET keyword for system level variables, and I imagine PostgreSQL is similar to Oracle...
OMG Ponies
2010-07-14 19:38:37
+4
A:
Those variables are part of Transact-SQL, not the SQL standard.
SQL was not designed to write procedural code, so its support for anything other than set operations is largely bolted on in proprietary extensions.
Cade Roux
2010-07-14 19:30:55
+3
A:
why do your need a "@" at the beginning of a variable name? to help parse the commands
The SET or SELECT helps differentiate assignment from comparisons, it is just the way TSQL was designed.
use:
SET @i=100 --for single assignments
and
SELECT @i=100,@a=200,@c=300 --for multiple assignements, which is
--faster than the equivalent multiple SET commands
KM
2010-07-14 19:35:22
Wait? Do you mean it wasn't to deliberately annoy me, but rather someone else?!?!?
Jason D
2010-07-15 13:09:56
+2
A:
The first reason that comes to mind is that SQL uses the '='-sign for both comparing of values as setting of values. This means that it requires a different syntax to distinguish between comparing and setting of the values. In (some) other programming there is a distinction between setting and comparing the values with the use of the '='-sign and the '=='-sign. For example in SQL:
SET @a = 100 --To set the value of @a.
if (@a = 100) -- To compare the value of @a.
For example in C#:
a = 100; //To set the value of a.
if (a == 100) //To compare the value of a.
Geert Immerzeel
2010-07-15 13:08:33