Add the possibility to declare constants within blocks

[VolkerBarth]

V12 has introduced the possibility to initialize local variables within the DECLARE statement, such as

declare lCnt integer = 0;

Now it would be fine if one could declare constants, too, (like "const int" in C/C++ etc.). This would be handy for longer blocks of code, e.g. to name savepoints or name expressions that are used in several queries.

And it might make explicit that a variable like in this question cannot be changed unintentionally...

I don't know if constants are defined in the SQL standards, so I won't suggest a particular syntax.

[MarkCulp · Accepted solution]

I like this idea and will discuss it with the SA development team for possible inclusion in a future release. Thanks for making the suggestion. As mentioned in Ivan's response to this question, the optimizer cannot use variable values when optimizing queries define within a stored procedure, but with the ability to mark a variable as constant (and if its value is, in fact, a constant) then there is the possibility that the optimizer could use the constant value at optimization time.

[VolkerBarth · Accepted solution]

Good news:) BTW, are named constants specified in the current SQL standards?

[MarkCulp · Accepted solution]

I glanced through the SQL 2008 ANSI standard and could not see anything that allowed declaration of a variable as a constant.

[VolkerBarth · Accepted solution]

@Mark: As many PLs support the notion of a "constant" it's interesting that this seems uncommon w.r.t. SQL. – Well, that makes me consider this a "cutting-edge suggestion":)