Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

How to use global variables in SQL Server ?

Hi All,
  How can I use global variables in SQL Server stored procedures ?

My aim is to make a value available throughout the duration of connection, so that  once the variable is initialised in a stored procedure,the variable can be accessed by any trigger associated with the tables used by the stored procedure .

Thanks,
Ras
0
rasaraja
Asked:
rasaraja
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This is not possible as such.
A workaround is to have a table where you store this value, with a keyindication of the @@spid value.

CREATE TABLE GlobalValues ( spid as int, value as varchar (100) )


in your stored procedure, you do this:

<...>
UPDATE GlobalValues SET value = 'somevalue' WHERE spid = @@spid
IF @@rowcount = 0
INSERT INTO GlobalValues (spid, value) VALUES (@@spid, 'somevalue')

<...>

in the other stored procedures or triggers, you can read the value:
DECLARE @val varchar(100)
SELECT @val = value from GlobalValues WHERE spid = @@spid
<...>

CHeers

0
 
niklausjCommented:
Another Workaround is to define a global cursor, this cursortype is visible to and valid during a connection.
you can set the cursor ( ... for select VALUE ) to return a single value, or set it to a temp-table-variable which will go out of scope when the cursor closes and that is either the close/deallocate statement or the closing of the connection.
0
 
rasarajaAuthor Commented:
Hi,
Both of you have given correct answers. How can I divide the point between you two ?
Thanks,
Ras
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Technically, you can either choose...  
* the first one
* the one you use
...or you ask for a point split (for 45 points?)
* post a 0-point question in the Community support area.
CHeers
0
 
CleanupPingCommented:
rasaraja:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now