Possible with multiple database connections

Question

New to the tSQLt world (great tool set) and encountered a minor issue with a stored procedure I am setting up a test for.

If I for some reason have a stored procedure which connects to mutiple databases or even multiple SQL servers (Linked Servers).

Is it possible to do unit tests with tSQLt in such a scenario?

Answer 1

I commented already, but I would like to add some more. So as I said already, that you can do anything that fits into the single transaction.

But for your case I would suggest to create synonyms for every cross database/instance object and then use synonyms everywhere.

I've created following function to mock view/tables synonyms. It has some limitations but at least it can handle simple use cases.

CREATE PROCEDURE [tSQLt].[FakeSynonymTable] @SynonymTable VARCHAR(MAX)
AS
     BEGIN

         DECLARE @NewName VARCHAR(MAX)= @SynonymTable+REPLACE(CAST(NEWID() AS VARCHAR(100)), '-', '');
         DECLARE @RenameCmd VARCHAR(MAX)= 'EXEC sp_rename '''+@SynonymTable+''', '''+@NewName+''';';

        EXEC tSQLt.SuppressOutput
              @RenameCmd;

        DECLARE @sql VARCHAR(MAX)= 'SELECT * INTO '+@SynonymTable+' FROM '+@NewName+' WHERE 1=2;';

        EXEC (@sql);

        EXEC tSQLt.FakeTable
              @TableName = @SynonymTable;
     END;