SQL Server Performance Forum – Threads Archive

Variable and exception declarations

1)I read somewhere that only local variables are supported – I can declare them in a procedure but not anywhere else. Is this correct? 2)Is there any way to force a variable to be constant, or do I just rely on nobody changing it? 3)If I wish to register my own error code+description, can this only be done inside a procedure? If so does is that registration only present inside that procedure? It seems poor if an error must be registered each time a procedure is run! Thanks guys.

---

1. SQL Server supports global variable also. You can delcare global variable by prefixing @@ to a vriableName. for eg. Declare @@GlobalVariableName varchar(10) But i never used this feature. 2. You can create Scalar function which can be used as a COnstant 3. You can add you own error message to sysmessage table by sp_addmessage system storedprocedure and later u can use this from sysmessage. Read about this in BOL. Madhu

---

Madhu, SQL server does not supports global variables. It is just that it allows you to create a variable with the name [@@GlobalVariableName], but its local in scope. In T-SQL context, a global variable is the system variables like @@ERROR, @@ROWCOUNT etc, which also known as globa functions. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Just to elaborate, by global I don’t mean truly global, but any variable declared outside of a procedure. So if I have a script with a bunch of procedures, I’d like to have some constants which can be used by each procedure.

---

hi Roji, thanks for the info… i never used global variable in sql, but my understanding was @@varable is global in scope. probably, got confused by the @@ prefix. Thanks again Madhu

---

You may have to create a Constants table and access this table from all of the procedures. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

]]>