Using db names in dynamic SQL select statement

Question

Apologies all, I am relatively new to dynamic SQL and I could do with some help please? I am struggling with making my query loop between databases.

I can get the query to run and return results but it's not moving between databases

--
DECLARE @HUB_Instance VARCHAR(25);
DECLARE cur_collectHubData CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name
FROM sys.databases
WHERE name LIKE '%HUB%'
      AND name NOT IN ( 'HUB_Training', 'HUB_Training_TNHG' );

OPEN cur_collectHubData;

FETCH NEXT FROM cur_collectHubData
INTO @HUB_Instance;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql1 NVARCHAR(2000);
    SET @sql1
       = 'SELECT t1.Company,
       t1.StaffNumber,
       t1.FullName,
       COUNT(t1.FullName) AS NumberOfCases
FROM
(       SELECT tbl_Company.Name AS Company,
           IdCase,
           FullName AS CaseStatus,
           IdUser_Core_Negotiator,
           StaffNumber,
           FullName
    FROM dbo.tbl_PreCallCaseDetails
        LEFT JOIN dbo.tbl_Case
            ON tbl_Case.Id = tbl_PreCallCaseDetails.IdCase
        JOIN dbo.tbl_CaseStatus
            ON tbl_CaseStatus.Id = tbl_Case.IdCaseStatus
        JOIN Core..tbl_User
            ON tbl_User.Id = dbo.tbl_PreCallCaseDetails.IdUser_Core_Negotiator
        JOIN core..tbl_Company 
            ON  tbl_Company.Id = Core..tbl_User.IdCompany
    WHERE IdUser_Core_Negotiator IS NOT NULL
) t1
GROUP BY t1.Company,
         t1.FullName,
         t1.StaffNumber;';

    EXEC sys.sp_executesql @sql1;

    FETCH NEXT FROM cur_collectHubData
    INTO @HUB_Instance;
END;

CLOSE cur_collectHubData;
DEALLOCATE cur_collectHubData;



-- Doesn't loop between databases
DECLARE @HUB_Instance VARCHAR(25);
DECLARE cur_collectHubData CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name
FROM sys.databases
WHERE name LIKE '%HUB%'
      AND name NOT IN ( 'HUB_Training', 'HUB_Training_TNHG' );

OPEN cur_collectHubData;

FETCH NEXT FROM cur_collectHubData
INTO @HUB_Instance;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql1 NVARCHAR(2000);
    SET @sql1
       = N'SELECT t1.Company,
       t1.StaffNumber,
       t1.FullName,
       COUNT(t1.FullName) AS NumberOfCases
FROM
(       SELECT tbl_Company.Name AS Company,
           IdCase,
           FullName AS CaseStatus,
           IdUser_Core_Negotiator,
           StaffNumber,
           FullName
    FROM' + @HUB_Instance + N'.[dbo].tbl_PreCallCaseDetails
        LEFT JOIN' + @HUB_Instance + N'.[dbo].tbl_Case
            ON tbl_Case.Id = tbl_PreCallCaseDetails.IdCase
        JOIN' + @HUB_Instance + N'.tbl_CaseStatus
            ON tbl_CaseStatus.Id = tbl_Case.IdCaseStatus
        JOIN Core..tbl_User
            ON tbl_User.Id = dbo.tbl_PreCallCaseDetails.IdUser_Core_Negotiator
        JOIN core..tbl_Company 
            ON  tbl_Company.Id = Core..tbl_User.IdCompany
    WHERE IdUser_Core_Negotiator IS NOT NULL
) t1
GROUP BY t1.Company,
         t1.FullName,
         t1.StaffNumber;';

    EXEC sys.sp_executesql @sql1;

I am a little confused on the point of this. You are just selecting data for a number of databases. But you have now way of knowing which database a given result set is from. — Sean Lange, May 24 '18 at 20:20
Why did you include the top half of your code in your question? The first cursor doesn't make any attempt to use the `@HUB_Instance` variable, so it doesn't really play any part in your issue. — Tab Alleman, May 24 '18 at 20:22
Apologies, if it caused confusion. I was trying to demonstrate I'd tried before asking for help. Thanks for the guidance though — jonny mathias, May 26 '18 at 16:59

S3S · Answer 1 · 2018-05-24T20:32:34.740

Your second cursor doesn't have a FETCH NEXT, or an END. You also didn't CLOSE or DEALLOCATE the cursor. As Sean pointed out, It is also missing a space before every single instance of @HB_Instance in the dynamic sql

DECLARE @HUB_Instance VARCHAR(25);
DECLARE cur_collectHubData CURSOR FAST_FORWARD READ_ONLY FOR
SELECT name
FROM sys.databases
WHERE name LIKE '%HUB%'
      AND name NOT IN ( 'HUB_Training', 'HUB_Training_TNHG' );

OPEN cur_collectHubData;

FETCH NEXT FROM cur_collectHubData
INTO @HUB_Instance;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql1 NVARCHAR(2000);
    SET @sql1
       = N'SELECT t1.Company,
       t1.StaffNumber,
       t1.FullName,
       COUNT(t1.FullName) AS NumberOfCases
FROM
(       SELECT tbl_Company.Name AS Company,
           IdCase,
           FullName AS CaseStatus,
           IdUser_Core_Negotiator,
           StaffNumber,
           FullName
    FROM ' + @HUB_Instance + N'.[dbo].tbl_PreCallCaseDetails
        LEFT JOIN ' + @HUB_Instance + N'.[dbo].tbl_Case
            ON tbl_Case.Id = tbl_PreCallCaseDetails.IdCase
        JOIN ' + @HUB_Instance + N'.tbl_CaseStatus
            ON tbl_CaseStatus.Id = tbl_Case.IdCaseStatus
        JOIN Core..tbl_User
            ON tbl_User.Id = dbo.tbl_PreCallCaseDetails.IdUser_Core_Negotiator
        JOIN core..tbl_Company 
            ON  tbl_Company.Id = Core..tbl_User.IdCompany
    WHERE IdUser_Core_Negotiator IS NOT NULL
) t1
GROUP BY t1.Company,
         t1.FullName,
         t1.StaffNumber;';

    EXEC sys.sp_executesql @sql1;
    --added everything below this line
    FETCH NEXT FROM cur_collectHubData
    INTO @HUB_Instance;
END;

CLOSE cur_collectHubData;
DEALLOCATE cur_collectHubData;

However, I'd suggest using QUOTENAME() around those table variables just for good measure.

It is also missing a space before every single instance of @HB_Instance in the dynamic sql. — Sean Lange, May 24 '18 at 20:18

Using db names in dynamic SQL select statement

1 Answers1