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.
asked 13 Jul '10, 02:44
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:
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.
answered 13 Jul '10, 09:47