Storing linked server in stored procedure variable

Question

I have following setup:

Server 1: CGENMONOSAMP01

Server 2: CGENMONOSAMP02

I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1:

SELECT * FROM [CGENMONOSAMP02].master.dbo.sysdatabases

And it retrieves list of DB's on Server 2.

I am trying to create the following:

    DECLARE @SourceDatabase varchar(50) = '[CGENMONOSAMP02].Sample_DB'
    DECLARE @SourceHost varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 2, 14))
    DECLARE @SourceDB varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 18, 100))

--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB

    IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @SourceDatabase)
    BEGIN
        IF NOT EXISTS (SELECT [name] FROM @SourceHost.master.dbo.sysdatabases WHERE [name] = @SourceDB) 
        BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDatabase)
        RETURN
        END
    END

This is being called on Server 1, it's supposed to take DB name which can either be name of DB located

on Server 1 as: "Sample_Table"

or it can be name of DB located

on Server 2 as: "[CGENMONOSAMP02].Sample_Table"

It's supposed to first check if passed name of DB exists on local server, if not - it should check if it exists on linked server.

I've tried the above and it gives me:

Incorrect syntax near '.'.

I've also tried:

IF NOT EXISTS (SELECT [name] FROM [@SourceHost].master.dbo.sysdatabases WHERE [name] = @SourceDB) 

But that gives me:

Could not find server '@SourceHost' in sys.servers. Verify that the correct server name was specified.

Is it possible to make such reference?

Answer 1

You could use dynamic sql to check the existence of databases in linked servers when using parameters.

An example:

DECLARE @SourceHost nvarchar(50) = '[CGENMONOSAMP02]';
DECLARE @SourceDB nvarchar(50) = 'Sample_DB';
--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB
CREATE TABLE #temp ([name] nvarchar(50));
IF NOT EXISTS (SELECT [name] FROM master.sys.databases WHERE [name] = @SourceDB)
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT [name] FROM '+@SourceHost+'.[master].[sys].[databases] WHERE [name] = @SourceDB ';

    INSERT INTO #temp([name])
    EXEC SP_EXECUTESQL @SQL,N'@SourceDB nvarchar(50)',@SourceDB;

    IF NOT EXISTS(SELECT [name] FROM #temp)
    BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDB);
    END
END

DROP TABLE #temp;