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

Questions

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

August 4, 2022

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".

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

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

>Solution :

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

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);

Not the dbname and SCHEMA and then the table name.

see:

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