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.
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.
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.
You Should specify the whole name with schema
for example
SELECT * FROM DB1.dbo.MyTable
