How to set an SQL variable from a query against DatabaseA and use the variable in a query against da-CodePudding

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

CodePudding user response：

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/

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