Hi,

What is the difference between the DECLARE statement and the CREATE VARIABLE Statement? And is it general practice to run a DROP VARIABLE at the end of the script when using these statements?

I read the online SQL manual for these but I still can't distinguish the difference in functionality apart from the DECLARE being used between a BEGIN and END.

Thanks,

Warren.

asked 13 Jul '10, 02:44

warren's gravatar image

warren
146455
accept rate: 0%


I would say a variable created with CREATE VARIABLE is a connection-specific global variable, i.e. it is accessible by all SQL statements of the current connection and can be changed by all these statements. There can only be one variable of that particular name at any time on one connection.

One would usually drop that variable when it is no more used. When the connection is closed, the variable is dropped automatically.

In contrast, DECLARE creates a local variable with block scope, i.e. it is visible and accessible within its block. AFAIK no variable of that particular name can be used in inner blocks (in constrast to typical programming languages where a variable v in an inner block "hides" a variable v in an outer block). (Note: I haven't tested that behaviour myself). Otherwise, this is the common kind of variable to use in stored procedures and the like.

As to the usage:

I would recommend to use local variables whenever possible.

CREATE VARIABLE is useful (and sometimes the most fitting approach) when you have to change behaviour on a "global scope", usually in more advanced situations. It often works like some kind of option. Some examples:

  • One can use such a variable to change the behaviour of many/all triggers - cf. this question.
  • One could filter results based on a connection-specific values (say, to list only the customers of a particular sales person) by including a filter value in the variable and use that in the WHERE clause of all according queries.

Besides that, in my opinion a global varible in SQL is possibly as "dangerous" as in other programming languages and should be handled with according care.

permanent link

answered 13 Jul '10, 09:47

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

2

Volker: I just want to say Thanks for all the material you have posted in this site. I have learned a lot from your answers as well as your questions. You have added a lot to my knowledge of SQLA.

(13 Jul '10, 15:39) Bill Aumen

@Bill: That's really nice to hear - thank you! I feel that sharing what one is about to learn or has just learnt is usually as valueable as sharing one's sound knowledge. I'm glad that this site is about both sides of the story... And frankly, I have learnt much more about SQL Anywhere since this site has been created than I'v learnt in the time before, too.

(13 Jul '10, 20:41) Volker Barth

Ahh, thanks for the detailed explanation. It makes sense now!

(27 Jul '10, 04:35) warren
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×5
×3

question asked: 13 Jul '10, 02:44

question was seen: 1,875 times

last updated: 13 Jul '10, 09:47