SQL Server Performance

Variable and exception declarations

Discussion in 'SQL Server 2005 General Developer Questions' started by jdx, Feb 2, 2007.

  1. jdx New Member

    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.
  2. madhuottapalam New Member

    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
  3. Roji. P. Thomas New Member

    quote:Originally posted by madhuottapalam

    1. SQL Server supports global variable also. You can delcare global variable by prefixing @@ to a vriableName. for eg.

    Declare @@GlobalVariableName varchar(10)
    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
  4. jdx New Member

    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.
  5. madhuottapalam New Member

    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
  6. Roji. P. Thomas New Member

    quote:Originally posted by jdx

    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.

    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

Share This Page