Running SQL across Several databases

Question

I understand I cant use [USE] statement within a sproc, however I need to query across several databases, this works great stand alone, how can I adapt this query to run across specified databases.

Would I have to Union all and specify each database?

Any Help is greatly appreciated /* Intalytics section for TB*/

BEGIN DECLARE @STARTDATE AS DATE = '2022-01-01' ,@ENDDATE AS DATE = '2022-02-28';

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb.dbo.#TB') IS NOT NULL
    DROP TABLE #TB;

DECLARE @intcount INT
DECLARE @DATABASENAME NVARCHAR(25)

SET @intcount = 1

CREATE TABLE #TB (
    Current_Database VARCHAR(50)
    ,Acct VARCHAR(50)
    ,AcctName NVARCHAR(100)
    ,[Month] DATETIME
    ,Oppening_Balance NUMERIC(18, 2)
    ,[Debit] NUMERIC(18, 2)
    ,[Credit] NUMERIC(18, 2)
    ,[Balance] NUMERIC(18, 2)
    ,[CBalance] NUMERIC(18, 2)
    )

CREATE CLUSTERED INDEX ix_tempCIcOMPANY ON #TB (Acct)

WHILE (@intcount <= 23)
BEGIN
    IF @intcount = 1
    BEGIN
        SET @DATABASENAME = 'KSS_INC'

        USE [KSS_INC]
    END

    IF @intcount = 2
    BEGIN
        SET @DATABASENAME = 'KSS_LTD'

        USE [KSS_LTD]
    END

    IF @intcount = 3
    BEGIN
        SET @DATABASENAME = 'MPSI_US'

        USE [MPSI_US]
    END

    IF @intcount = 4
    BEGIN
        SET @DATABASENAME = 'KENTGRPLTD'

        USE [KENTGRPLTD]
    END

    IF @intcount = 5
    BEGIN
        SET @DATABASENAME = 'KSS_INDIA'

        USE [KSS_INDIA]
    END

    IF @intcount = 6
    BEGIN
        SET @DATABASENAME = 'KSS_Canada'

        USE [KSS_Canada]
    END

    IF @intcount = 7
    BEGIN
        SET @DATABASENAME = 'KSS_AUS'

        USE [KSS_AUS]
    END

    IF @intcount = 8
    BEGIN
        SET @DATABASENAME = 'MPS_INDIA'

        USE [MPS_INDIA]
    END

    IF @intcount = 9
    BEGIN
        SET @DATABASENAME = 'MPSI_SAF'

        USE [MPSI_SAF]
    END

    IF @intcount = 10
    BEGIN
        SET @DATABASENAME = 'MPSI_CAN'

        USE [MPSI_CAN]
    END

    IF @intcount = 11
    BEGIN
        SET @DATABASENAME = 'MPSI_JAP'

        USE [MPSI_JAP]
    END

    IF @intcount = 12
    BEGIN
        SET @DATABASENAME = 'MPSI_UK'

        USE [MPSI_UK]
    END

    IF @intcount = 13
    BEGIN
        SET @DATABASENAME = 'MPSI_CHI'

        USE [MPSI_CHI]
    END

    IF @intcount = 14
    BEGIN
        SET @DATABASENAME = 'KAL_LTD'

        USE [KAL_LTD]
    END

    IF @intcount = 15
    BEGIN
        SET @DATABASENAME = 'TRADE_AREA_SYSTEMS'

        USE [TRADE_AREA_SYSTEMS]
    END

    IF @intcount = 16
    BEGIN
        SET @DATABASENAME = 'INTALYTICS'

        USE [INTALYTICS]
    END

    IF @intcount = 17
    BEGIN
        SET @DATABASENAME = 'KSSL_LTD'

        USE [KSSL_LTD]
    END

    IF @intcount = 18
    BEGIN
        SET @DATABASENAME = 'MPSI'

        USE [MPSI]
    END

    IF @intcount = 19
    BEGIN
        SET @DATABASENAME = 'MPSI_INT'

        USE [MPSI_INT]
    END

    IF @intcount = 20
    BEGIN
        SET @DATABASENAME = 'MPSI_KOR'

        USE [MPSI_KOR]
    END

    IF @intcount = 21
    BEGIN
        SET @DATABASENAME = 'NEW_SIS_SA'

        USE [NEW_SIS_SA]
    END

    IF @intcount = 22
    BEGIN
        SET @DATABASENAME = 'MPSICHI_INC'

        USE [MPSICHI_INC]
    END

    IF @intcount = 23 -- to stop loop after last database
    BEGIN
        BREAK
    END

    INSERT INTO #TB
    SELECT @DATABASENAME
        ,T1.Account
        ,T2.AcctName
        ,CONCAT (
            DATENAME(MONTH, T1.RefDate)
            ,' '
            ,YEAR(T1.REFDATE)
            ) AS [Month]
        ,Isnull((
                SELECT SUM(T3.Debit - T3.Credit)
                FROM dbo.OJDT T2
                INNER JOIN dbo.JDT1 T3 ON T2.TransId = T3.TransId
                WHERE DateDiff(dd, T2.RefDate, @StartDate) > 0
                    AND T3.Account LIKE T1.Account
                GROUP BY T3.Account
                ), 0) 'Opening balance'
        ,SUM(T1.Debit) 'Debit'
        ,SUM(T1.Credit) 'Credit'
        ,SUM(T1.Debit - T1.Credit) AS 'Balance'
        ,Isnull((
                SELECT SUM(T3.Debit - T3.Credit)
                FROM dbo.OJDT T2
                INNER JOIN dbo.JDT1 T3 ON T2.TransId = T3.TransId
                WHERE DateDiff(dd, T2.RefDate, @StartDate) > 0
                    AND T3.Account LIKE T1.Account
                GROUP BY T3.Account
                ), 0) + SUM(T1.Debit - T1.Credit) AS 'CBalance'
    --- CB 
    FROM OJDT T0
    INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
    LEFT JOIN dbo.OACT T2 ON T2.AcctCode = T1.Account /*added to bring through */
    WHERE T0.RefDate BETWEEN @StartDate
            AND @EndDate
    GROUP BY T1.Account
        ,T2.AcctName
        ,CONCAT (
            DATENAME(MONTH, T1.RefDate)
            ,' '
            ,YEAR(T1.REFDATE)
            )
        ,LEFT(DATENAME(MONTH, t1.RefDate), 3) + ' ' + RIGHT('00' + CAST(YEAR(t1.RefDate) AS VARCHAR), 2)
    HAVING SUM(T1.Debit - T1.Credit) != 0

    SET @intcount = @intcount + 1
END

SELECT *
FROM #TB AS t

END

Answer 1

DECLARE @SQL nvarchar
SET @SQL =''
WHILE (@intcount <= 23)
BEGIN
IF @intcount = 1
BEGIN
    SET @DATABASENAME = 'KSS_INC'

    USE [KSS_INC]
END

IF @intcount = 2
BEGIN
    SET @DATABASENAME = 'KSS_LTD'

    USE [KSS_LTD]
END

IF @intcount = 3
BEGIN
    SET @DATABASENAME = 'MPSI_US'

    USE [MPSI_US]
END

IF @intcount = 4
BEGIN
    SET @DATABASENAME = 'KENTGRPLTD'

    USE [KENTGRPLTD]
END

IF @intcount = 5
BEGIN
    SET @DATABASENAME = 'KSS_INDIA'

    USE [KSS_INDIA]
END

IF @intcount = 6
BEGIN
    SET @DATABASENAME = 'KSS_Canada'

    USE [KSS_Canada]
END

IF @intcount = 7
BEGIN
    SET @DATABASENAME = 'KSS_AUS'

    USE [KSS_AUS]
END

IF @intcount = 8
BEGIN
    SET @DATABASENAME = 'MPS_INDIA'

    USE [MPS_INDIA]
END

IF @intcount = 9
BEGIN
    SET @DATABASENAME = 'MPSI_SAF'

    USE [MPSI_SAF]
END

IF @intcount = 10
BEGIN
    SET @DATABASENAME = 'MPSI_CAN'

    USE [MPSI_CAN]
END

IF @intcount = 11
BEGIN
    SET @DATABASENAME = 'MPSI_JAP'

    USE [MPSI_JAP]
END

IF @intcount = 12
BEGIN
    SET @DATABASENAME = 'MPSI_UK'

    USE [MPSI_UK]
END

IF @intcount = 13
BEGIN
    SET @DATABASENAME = 'MPSI_CHI'

    USE [MPSI_CHI]
END

IF @intcount = 14
BEGIN
    SET @DATABASENAME = 'KAL_LTD'

    USE [KAL_LTD]
END

IF @intcount = 15
BEGIN
    SET @DATABASENAME = 'TRADE_AREA_SYSTEMS'

    USE [TRADE_AREA_SYSTEMS]
END

IF @intcount = 16
BEGIN
    SET @DATABASENAME = 'INTALYTICS'

    USE [INTALYTICS]
END

IF @intcount = 17
BEGIN
    SET @DATABASENAME = 'KSSL_LTD'

    USE [KSSL_LTD]
END

IF @intcount = 18
BEGIN
    SET @DATABASENAME = 'MPSI'

    USE [MPSI]
END

IF @intcount = 19
BEGIN
    SET @DATABASENAME = 'MPSI_INT'

    USE [MPSI_INT]
END

IF @intcount = 20
BEGIN
    SET @DATABASENAME = 'MPSI_KOR'

    USE [MPSI_KOR]
END

IF @intcount = 21
BEGIN
    SET @DATABASENAME = 'NEW_SIS_SA'

    USE [NEW_SIS_SA]
END

IF @intcount = 22
BEGIN
    SET @DATABASENAME = 'MPSICHI_INC'

    USE [MPSICHI_INC]
END

IF @intcount = 23 -- to stop loop after last database
BEGIN
    BREAK
END

   SET @SQL =' INSERT INTO #TB
SELECT' + @DATABASENAME +'
    ,T1.Account
    ,T2.AcctName
    ,CONCAT (
        DATENAME(MONTH, T1.RefDate)
        ,'' ''
        ,YEAR(T1.REFDATE)
        ) AS [Month]
    ,Isnull((
            SELECT SUM(T3.Debit - T3.Credit)
            FROM  '+@DATABASENAME+'.dbo.OJDT T2
            INNER JOIN  '+@DATABASENAME+'.dbo.JDT1 T3 ON T2.TransId = T3.TransId
            WHERE DateDiff(dd, T2.RefDate, '+@StartDate+') > 0
                AND T3.Account LIKE T1.Account
            GROUP BY T3.Account
            ), 0) ''Opening balance''
    ,SUM(T1.Debit) ''Debit''
    ,SUM(T1.Credit) ''Credit''
    ,SUM(T1.Debit - T1.Credit) AS ''Balance''
    ,Isnull((
            SELECT SUM(T3.Debit - T3.Credit)
            FROM '+@DATABASENAME+'.dbo.OJDT T2
            INNER JOIN  '+@DATABASENAME+'.dbo.JDT1 T3 ON T2.TransId = T3.TransId
            WHERE DateDiff(dd, T2.RefDate, '+@StartDate+') > 0
                AND T3.Account LIKE T1.Account
            GROUP BY T3.Account
            ), 0) + SUM(T1.Debit - T1.Credit) AS ''CBalance''
--- CB 
FROM  '+@DATABASENAME+'..OJDT T0
INNER JOIN  '+@DATABASENAME+'..JDT1 T1 ON T0.TransId = T1.TransId
LEFT JOIN '+@DATABASENAME+'.dbo.OACT T2 ON T2.AcctCode = T1.Account 
WHERE T0.RefDate BETWEEN '+@StartDate+'
        AND '+@EndDate+'
GROUP BY T1.Account
    ,T2.AcctName
    ,CONCAT (
        DATENAME(MONTH, T1.RefDate)
        ,'' ''
        ,YEAR(T1.REFDATE)
        )
    ,LEFT(DATENAME(MONTH, t1.RefDate), 3) + '' '' + RIGHT(''00'' + CAST(YEAR(t1.RefDate) AS VARCHAR), 2)
HAVING SUM(T1.Debit - T1.Credit) != 0

SET '+@intcount+' = '+@intcount+' + 1
END

SELECT *
FROM  '+@DATABASENAME+'..#TB AS t'
exec(@SQL)