Global Temporary Variables in SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Global Temporary Variables in SQL server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-16 : 08:09:28

Deepa writes "I have package spec defined in ORACLE as follows in which I declare a global temporary variable.

CREATE OR REPLACE PACKAGE TESTPKG AS
CTR NUMBER(3) := 0;
END TESTPKG;

**************************************************************

I have a table defined as follows :

CREATE TABLE TESTC(ctr number(3));

**************************************************************
I have a proc in which I call this variable for the purpose of Insertion into a table TestC.

CREATE OR REPLACE PROCEDURE TESTPROC AS
BEGIN
TESTPKG.CTR := TESTPKG.CTR + 1;
INSERT INTO TESTC VALUES(TESTPKG.CTR);
COMMIT;
END TESTPROC;

**************************************************************
Now when I execute Procedure TESTPROC for the

(a) First Time :

select * from TestC gives me 1

(b) Second Time :

select * from TestC gives me 2

(c) Third Time

select * from TestC gives me 3

I disconnect the session and re-connect

Now I again execute the proc and do a select * from testC
and this will now yield me 1

My question is :

What is the Equivalent or work-around for handling this situation in MS-SQL server."

Crito
Starting Member

40 Posts

Posted - 2005-06-16 : 10:35:18

A global variable name in Transact-SQL must begin with @@ and a global temp table name must begin with ##, as opposed to a local var that begins with a single @ and a local temp table that begins with single #. Both are bound to the specific connection/session and those resources are automatically freed when its disconnected/closed, just like in Oracle.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources