How to define global var?

How to I create a global variable for several SPs to share? For example, I
might have two status vars, such as statusred = 3 and statusgreen = 1.
Thanks,
BrettInsert the value(s) in a table and have each of your stored procs select the
value from the table?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Brett" <no@.spam.com> wrote in message
news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>|||That's one way but isn't that inefficient?
How does SQL Server use the @.@.ERROR global var for example?
Thanks,
Brett
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
> Insert the value(s) in a table and have each of your stored procs select
> the
> value from the table?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Brett" <no@.spam.com> wrote in message
> news:%23YLgthPIFHA.1176@.TK2MSFTNGP12.phx.gbl...
> I
>|||Do the two SPs have anything in common? i.e., do they run in support of one
another? or in rsponse to the same trigger? or are they totally indodependan
t
except for their use of this same value?
If they're functionally related, consider creating a single SP That aclls
both of them, and have that SP pass the value to both SPs that need it.
If they're not, it sounds like what you have is (one of potentially many)
application configuration settings. These can be stored and propogated to
wherever they are needed in a variety of ways, including externally in XML
files, or the Registry, or internally in a separate Database Table that has
name value pairs (Setting, value).
Don;t worry about efficiency ( I Think you meant performance) because SQL is
optimized for this. If the value is used often, it will be cached and kept
in memory anyway.
"Brett" wrote:

> How to I create a global variable for several SPs to share? For example,
I
> might have two status vars, such as statusred = 3 and statusgreen = 1.
> Thanks,
> Brett
>
>|||"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
No; SQL Server will keep the value in memory if it's accessed often --
the in-memory cache frees data based on usage, so keep using it and it
stays.
As for @.@.ERROR, that's a function, not a global variable. It's just
named similarly to a variable.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||From BOL ...@.@.ERROR is cleared and reset on each statement executed...
Where did you get the idea that this is a global variable?
Why don't you just pass the 2 status's as parameters from 1 SP to the other?
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>|||Think about this for a moment. It is a relational database with the primary
goal of storing data in a table. The whole purpose is optimal data
handling. For a few values that you will be dealing with they will likely
be stored in memory throughout the process anyhow.
Just create a permanent table that your procs use and they can share data on
multiple connections. You will have to figure out how to handle garbage
collection when the programs stop and/or when the start however.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.com> wrote in message
news:O9k4M4PIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> That's one way but isn't that inefficient?
> How does SQL Server use the @.@.ERROR global var for example?
> Thanks,
> Brett
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uj8mZsPIFHA.3076@.TK2MSFTNGP10.phx.gbl...
>