How to set an SQL variable from a query against DatabaseA and use the variable in a query against databaseB

Question

USE DATABASE_A
GO   
DECLARE @Action_ID AS UNIQUEIDENTIFIER = (SELECT Action_ID FROM Actions WHERE CodeNumber  = 'VCT-XXX-000001');
        
USE DATABASE_B
GO
DECLARE @Property_ID AS INT  = (SELECT Property_ID FROM [Properties] WHERE Action_ID  = Action_ID);

Above am declaring and setting variable @Action_ID with a query that selectes from table "Actions" which is in database DATABASE_A

I want to use @Action_ID in a query that will be executed against database DATABASE_B but that throws an error that says

"Must declare the scalar variable "@Action_ID".

How can i use variable @Action_ID in queries that will be executed against database DATABASE_B

A variable only persists for the **batch** it is declared in. As your latter query is in a different batch, then `@Action_ID` no longer exists. Either don't use a separate batch, or (possibly better) don't change database context and use 3 part naming for one query and 2 part (you are missing the schema in both) for the other query. — Thom A, Aug 04 '22 at 13:19
Are you intending to re-use the `@Action_ID` variable subsequently? If not just use your sub-query from the variable assignment directly in your second query with fully qualified names. — Stu, Aug 04 '22 at 13:30

Moe Sisko · Answer 1 · 2022-08-05T03:17:41.303

Another way (for cases when you must, or prefer to use multiple batches), might be to use SESSION_CONTEXT (for sqlserver 2016 and later).

Simplified example:

-- in batch 1.
declare @id uniqueidentifier = 'D256B043-FD10-4F96-BC28-FD4C473D45C2'

-- save @id to session_context
EXEC sp_set_session_context @key='id', @value = @id
GO
-- in another batch.
-- restore @id from session_context
declare @id uniqueidentifier = cast(SESSION_CONTEXT(N'id') as uniqueidentifier)

select @id as id -- should show: D256B043-FD10-4F96-BC28-FD4C473D45C2

score 1 · Accepted Answer · edited Nov 09 '22 at 22:23

You cannot do it with a "GO". (a "block delimiter" aka a "batch separator").

see:

What is the use of GO in SQL Server Management Studio & Transact SQL?

you can try fully qualified names:

DECLARE @Action_ID AS UNIQUEIDENTIFIER = (SELECT Action_ID FROM DATABASE_A.dbo.Actions WHERE CodeNumber  = 'VCT-XXX-000001');
        

DECLARE @Property_ID AS INT  = (SELECT Property_ID FROM DATABASE_B.dbo.[Properties] WHERE Action_ID  = Action_ID);

Note the dbname and SCHEMA and then the table name.

see:

https://www.tektutorialshub.com/sql-server/fully-qualified-table-names-in-sql-server/

The fully qualified table names in SQL Server consists of three parts. database name, schema name & the actual table name.

And now equipped with the "magic name/phrase" (sql+server+fully+qualified+table+name), you can internet search

https://www.google.com/search?q=sql+server+fully+qualified+table+name

How to set an SQL variable from a query against DatabaseA and use the variable in a query against databaseB

2 Answers2