I'm a relative newcomer to SQL Anywhere, and I keep finding myself beating my head against the DECLARE statement, hard as that might be to believe. Consider the following code:
DECLARE @A INTEGER = 1 DECLARE @B INTEGER = 2 CREATE LOCAL TEMPORARY TABLE MyIntegers ( MyInteger INTEGER ) INSERT INTO MyIntegers ( MyInteger ) VALUES (@A) INSERT INTO MyIntegers ( MyInteger ) VALUES (@B) SELECT * FROM MyIntegers
I rather expected the output of that result set to be two rows containing one and two. The actual output, however, is two rows containing NULL and two. I find it bizarre that the variable @A is defined but does not get initialized to the specified value. Interestingly, the following change to the DECLARE statement provides the expected output:
DECLARE @A INTEGER = 1, @B INTEGER = 2
What I don't understand is why repeating the DECLARE keyword fails as it does. Can somebody help me understand? Thanks in advance!
asked 05 Dec '12, 12:04
The difference is the SQL dialect again (as in the other question). Derly uses Watcom-SQL, which does work correct w.r.t. the separate DECLARE statements, i.e. it enters 1 and 2.
Your original sample is T-SQL, and it seems to be a bug/feature of the T-SQL DECLARE statement to (not) work that way... I can't tell whether this is expected or not, the docs don't treat the features of DECLARE to be dialect-dependent...
If you are using SQL Anywhere and not also using MS SQL or Sybase ASE (which use T-SQL), I'd hightly recommend to use the Watcom-SQL dialect.
BEGIN DECLARE @A INTEGER=1; DECLARE @B INTEGER=2; CREATE LOCAL TEMPORARY TABLE MyIntegers ( MyInteger INTEGER ); INSERT INTO MyIntegers ( MyInteger ) VALUES (@A); INSERT INTO MyIntegers ( MyInteger ) VALUES (@B); SELECT * FROM MyIntegers; END