CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Rich
Rich

Reputation: 3821

Is there a way to create your own @@variable within SQL Server?

In SQL server, there are times when I have a variable which may change, that many stored procedures all use and just think it would be cool if I could store it as an '@@variable' (like @@servername).

Is it possible to create your own '@@variable'? And if so, how do you do it?

(using sql server 2008)

Upvotes: 1

Views: 440

Answers (3)

Treborbob
Treborbob

Reputation: 1221

I'd go with using a real table to hold your global values, then it'll survive between restarts/connections. There is a good example of that here: http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx

Upvotes: 6

rickythefox
rickythefox

Reputation: 6861

You can't declare a global variable that's shareable between stored procedures.

You could however use global temporary tables to share data, just declare a table using syntax below and insert/read values.

CREATE TABLE ##myTempTable
(
  DummyField1 INT,
  DummyField2 VARCHAR(20)
)

To share data within the connection you can use context_info:

declare @vb varbinary(128)
set @vb = context_info() -- read value
set context_info @vb -- set value

Upvotes: 1

Tom H
Tom H

Reputation: 47402

In these situations I would generally have a table to hold these values and I would keep the settings there. You could use a scalar function to encapsulate it with a stored procedure to set it. I don't know of any way to actually create your own @@ variables, but I haven't looked into it.

Upvotes: 1

Related Questions