global vars in script files ?

11 Mar 2006 12:03 AM

John A Grandy

Is it possible to declare a global variable in a SQL script ... or some
workaround ?

[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)

In the following script, the local var @ptId is lost once a "GO" is
executed.

USE [ContentDB]

GO

INSERT INTO [dbo].[PageTypes]
              ([ptName]
              ,[ptPath]
              ,[ptParamName])
      VALUES
              ('unused'
              ,'/redirect.aspx'
              ,'url')

DECLARE @ptId int

SET @ptId = @@IDENTITY
..
..
..
..
<lots and lots of other SQL>
..
..
..
..
GO
..
..
..
..
<lots and lots of other SQL>
..
..
..

11 Mar 2006 6:35 AM

Tibor Karaszi

There are no global variables in TSQL. You can use a temp table for this, or check out SET
CONTEXT_INFO.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some workaround ?
>
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
>
> In the following script, the local var @ptId is lost once a "GO" is executed.
>
> USE [ContentDB]
>
> GO
>
> INSERT INTO [dbo].[PageTypes]
>              ([ptName]
>              ,[ptPath]
>              ,[ptParamName])
>      VALUES
>              ('unused'
>              ,'/redirect.aspx'
>              ,'url')
>
> DECLARE @ptId int
>
> SET @ptId = @@IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>