I also wanted to have a set of constants (at least global to a database).
I don't see a way to do it. From the fact that you posted in February (I just joined this forum), it looks like noone else found a solution.
I guess the best we can do is to cut and paste a section like:
DECLARE @OneConstant varchar(10)
DELCARE @AnotherConstant int
SELECT @OneConstant = "A string"
SELECT @AnotherConstant = 10
But you have to paste it into every routine, and if it changes, you have to change it for every routine. Not at all what we want.
If you hear of any way to do it, please post a reply.
We thought about storing constants in a database table, but decided against that. It would definitely improve maintainability, but increase code size and maybe complexity.
We rather solved our problem by making a small Perl-script that preprocesses the SQL source code. The line
in the SQL-code would be replaced by the contents of the named file.
All our procedures are version controlled and we use good old fashioned makefiles, so I just call my little preprocessor like in C when making:
sqlpre.pl file.sql > destcat/file.sql
It is not ideal:
- not very efficient, you will probably be declaring a lot more variables than you'll be using in each procedure
- the line numbers in the error messages won't be correct compared to the source file
- if you include multiple files where the same constant is declared twice (which of course shouldn't happen, but...) - ASE barfs.