Dynamic Database in SubQuery-CodePudding

SELECT
 email, password,GP_employee_id, company,                                                
 (select distinct CHEKNMBR from [BSL].[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20 ) as slip_number,
 (select distinct CONVERT(date , CHEKDATE) from [BSL].[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20 ) as slip_number
FROM [payslips].[dbo].[myapp_user]

I would like [BSL] to be dynamic. The value would depend on the company field of the main query. So I want something like this [company].[dbo].[UPR30300]

CodePudding user response：

DECLARE @sql VARCHAR(1000);
DECLARE @company VARCHAR(50);

DROP TABLE IF EXISTS #myapp_user;
CREATE TABLE #myapp_user 
(
    email           VARCHAR(256),
    [password]      VARCHAR(256),
    GP_employee_id  INT,
    slip_number     INT,
    slip_number2    INT
);

DECLARE db_cursor CURSOR FOR 
SELECT DISTINCT company FROM [payslips].[dbo].[myapp_user];

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @company  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      
    SET @sql = '
        SELECT
            email, 
            password,
            GP_employee_id,                                                
            slip_number = (select distinct CHEKNMBR from '   QUOTENAME(@company)   '.[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20),
            slip_number2 = (select distinct CONVERT(date , CHEKDATE) from '   QUOTENAME(@company)   '.[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20) 
        FROM 
            [payslips].[dbo].[myapp_user]';
        
    INSERT INTO #myapp_user EXECUTE (@sql);

    FETCH NEXT FROM db_cursor INTO @company;
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

SELECT * FROM #myapp_user;

CodePudding user response：

You can do one big dynamic UNION ALL query

DECLARE @unioned nvarchar(max) = (
    SELECT STRING_AGG(CAST(
'
    SELECT *, company = '   QUOTENAME(company, '''')    '
    FROM '   QUOTENAME(company)   '.[dbo].[UPR30300]
    WHERE CHEKDATE > DATEADD(day, -20, GETDATE())
'
      AS nvarchar(max)), 'UNION ALL')
    FROM (
        SELECT DISTINCT company
        FROM [payslips].[dbo].[myapp_user]
    ) au
);

DECLARE @sql nvarchar(max) = '
SELECT
    au.email,
    au.password,
    au.GP_employee_id,
    au.company,                                                
    slip_number = u.CHEKNMBR,
    slip_number2 = CONVERT(date, u.CHEKDATE)
FROM [payslips].[dbo].[myapp_user] au
LEFT JOIN (
'   @unioned   '
) u ON u.company = au.company
    AND u.EMPLOYID = au.GP_employee_id;
';

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;