CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Pavel Shkleinik
Pavel Shkleinik

Reputation: 6347

Pass EXEC statement to APPLY as a parameter

I have a need to grab data from multiple databases which has tables with the same schema. For this I created synonyms for this tables in the one of the databases. The number of databases will grow with time. So, the procedure, which will grab the data should be flexible. I wrote the following code snippet to resolve the problem:

WHILE @i < @count
BEGIN
    SELECT @synonymName = [Name]
    FROM Synonyms
    WHERE [ID] = @i
        SELECT @sql = 'SELECT TOP (1) * 
                FROM [dbo].[synonym' + @synonymName + '] as syn
                WHERE [syn].[Id] = tr.[Id]
                ORDER BY [syn].[System.ChangedDate] DESC'

        INSERT INTO @tmp
        SELECT  col1, col2
        FROM
        (
            SELECT * FROM TableThatHasRelatedDataFromAllTheSynonyms
            WHERE [Date] > @dateFrom 
        ) AS tr         
        OUTER APPLY (EXEC(@sql)) result 

    SET @i = @i + 1
END

I also appreciate for any ideas on how to simplify the solution.

Upvotes: 0

Views: 470

Answers (2)

Pavel Shkleinik
Pavel Shkleinik

Reputation: 6347

My solution is quite simple. Just to put all the query to the string and exec it. Unfortunately it works 3 times slower than just copy/past the code for all the synonyms.

WHILE @i < @count
BEGIN
    SELECT @synonymName = [Name]
    FROM Synonyms
    WHERE [ID] = @i
        SELECT @sql = 'SELECT  col1, col2
        FROM
        (
            SELECT * FROM TableThatHasRelatedDataFromAllTheSynonyms
            WHERE [Date] > ''' + @dateFrom + '''
        ) AS tr         
        OUTER APPLY (SELECT TOP (1) * 
                FROM [dbo].[synonym' + @synonymName + '] as syn
                WHERE [syn].[Id] = tr.[Id]
                ORDER BY [syn].[System.ChangedDate] DESC) result'

        INSERT INTO @tmp
        EXEC(@sql)

    SET @i = @i + 1
END

Upvotes: 0

oryol
oryol

Reputation: 5248

Actually, it's better to import data from all tables into one table (maybe with additional column for source table name) and use it. Importing can be performed through SP or SSIS package.

Regarding initial question - you can achieve it through TVF wrapper for exec statement (with exec .. into inside it).

UPD: As noticed in the comments exec doesn't work inside TVF. So, if you really don't want to change DB structure and you need to use a lot of tables I suggest to:

  1. OR select all data from synonym*** table into variables (as I see you select only one row) and use them
  2. OR prepare dynamic SQL for complete statement (with insert, etc.) and use temporary table instead of table variable here.

Upvotes: 1

Related Questions