T-SQL : using variable in different database-CodePudding

Trying to find a way to use a declared variable's result from one database to be used in a different database:

For example:

USE DB1
GO

DECLARE @d DATETIME

SELECT TOP 1 @d = Date 
FROM DateDB
 
USE DB2
GO

PRINT @d

Error:

Must declare the scalar variable "@d"

Not sure if it is possible, so figured Id ask S.O. after Googling (Perhaps incorrectly) for the last 45 minutes.

CodePudding user response：

Local variables only exist for the lifetime of a single batch.

In SSMS, the GO keyword (assuming it hasn't been altered in the settings) separates batches.

The statements following GO are a new separate batch; to persist data you will need to store it - you can create a table in TempDB for such a purpose.

CodePudding user response：

You Should specify the whole name with schema

for example

SELECT * FROM DB1.dbo.MyTable

CodePudding user response：

The go keyword is treated as a batch separator by SQL Server Management Studio and sqlcmd. It indicates the start of a new batch, and each new batch has its own variables.

However, there is a convenient storage for variables between databases in a single session: session storage. These context variables persist for the life of the session and can be referenced in multiple batches.

Use the sp_set_session_context system procedure to set a session context variable. Use the SESSION_CONTEXT() function to retrieve the session context variables.

For example:

USE DB1
GO

DECLARE @d DATETIME

SELECT @d = '1/1/2022'

PRINT @d
 
EXEC sp_set_session_context @key = N'MyDate', @value = @d

USE DB2
GO

DECLARE @d DATETIME
SELECT @d = CAST(SESSION_CONTEXT(N'MyDate') AS DATETIME)
PRINT @d

Output:

Jan  1 2022 12:00AM
Jan  1 2022 12:00AM

If you need to remember a variable across multiple sessions, then you must save the variable to a persistent storage like a tempdb table or global temporary ##Table.

For example, an initialization batch could save a date value in a tempdb table called tempdb..MyVariables column MyDate. Later sessions processing this information could refer to the value in this table column. A final cleanup session could remove the table completely.