EXEC sp_executesql: No cross server join

February 8, 2022

I’d like to execute following statement with a clause to prevent cross server joins:

SET @Sql = N'
    SELECT TOP(1) @CodeID = CodeType
    FROM ' + QUOTENAME(@Db) + '.bla.Field 
    WHERE Name = @SearchName'

EXEC sp_executesql @Sql,
    N'@SearchName NVARCHAR(256), @CodeID NVARCHAR(256) OUTPUT',
    @SearchName, @CodeID OUTPUT

For EXEC I use this statement:

  SET @Sql = 'EXEC (''' + REPLACE(@Sql, '''', '''''') + ''')' + CASE WHEN @ServerName = @ThisServer THEN '' ELSE ' AT ' + @ServerName END
    EXEC ( @Sql )

How do I adapt my statement to work with EXEC sp_executesql?

>Solution :

You can use the fact that the procedure name EXEC calls can be furnished via a variable. So that can be:

DECLARE @exec nvarchar(1000) = N'sys.sp_executesql';
EXEC @exec N'SELECT 1';

— or

DECLARE @exec nvarchar(1000) = QUOTENAME(@db) 
  + N'sys.sp_executesql';
EXEC @exec N'SELECT 1';

— or

DECLARE @exec nvarchar(1000) = QUOTENAME(@server)
  + QUOTENAME(@db) 
  + N'sys.sp_executesql';
EXEC @exec N'SELECT 1';

In your case:

DECLARE @Sql nvarchar(max) = N'
    SELECT TOP(1) @CodeID = CodeType
    FROM bla.Field 
    WHERE Name = @SearchName;';

DECLARE @exec nvarchar(1000) = CASE
  WHEN @ServerName = @ThisServer THEN N''
  ELSE QUOTENAME(@ServerName) + N'.' END
  + QUOTENAME(@db) + N'.sys.sp_executesql';

EXEC @exec @Sql,
    N'@SearchName NVARCHAR(256), @CodeID NVARCHAR(256) OUTPUT',
    @SearchName, @CodeID OUTPUT;