CODEWITHSUNDEEP
sql-servert-sql
StackTrace
StackTrace

Reputation: 9416

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

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

Upvotes: 0

Views: 76

Answers (2)

granadaCoder
granadaCoder

Reputation: 27904

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

Upvotes: 1

Moe Sisko
Moe Sisko

Reputation: 12051

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

Upvotes: 2

Related Questions