EXEC sp_executesql: No cross server join-CodePudding

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?

CodePudding user response：

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)   N'.'
    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;