SQL脚本优化：解决含VLData数据库中架构名匹配错误及子查询返回多行问题

Let's break down the issues in your original scripts and fix them step by step:

Key Problems in Your Original Code #

1. Wrong Schema Name Logic: The initial script used the database name prefix (e.g., 2544 from 2544VLData) as the schema name, which doesn't match your actual schema (like showoffstudios).
2. Incorrect Context for sys.database_principals: When you tried to fetch the schema/login name via sys.database_principals, you were querying the current database instead of the target VLData database. Additionally, the subquery could return multiple users, causing the "Subquery returned more than 1 value" error.

Fixed Script #

Here's the revised script that addresses both issues:

DECLARE @DB_LoginName varchar(200)
DECLARE @DB_Name sysname
DECLARE @Command nvarchar(MAX)
DECLARE @GetLoginCmd nvarchar(MAX)

-- Create temporary tables for results
create table #PaySlips (
 WgYr int,
 RunNo int,
 PaySlips int
);
create table #TotalNumber (
 Firmanavn varchar(100),
 Lønnsslipper int,
 Sammenstillingsoppgaver int,
 Lønnsår int
);

-- Cursor to iterate over VLData databases (using modern sys.databases instead of sys.sysdatabases)
DECLARE database_cursor CURSOR FOR SELECT name FROM sys.databases where name LIKE '%VLData%'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Dynamically switch to the target database and fetch the correct login/schema name
    SET @GetLoginCmd = N'USE [' + @DB_Name + N']; 
                         SELECT @LoginName = name 
                         FROM sys.database_principals 
                         WHERE type NOT IN (''A'', ''G'', ''R'', ''X'') 
                           AND sid IS NOT NULL 
                           AND name NOT IN (''guest'', ''dbo'')'
    -- Execute the dynamic query and output the result to @DB_LoginName
    EXEC sp_executesql @GetLoginCmd, 
                       N'@LoginName varchar(200) OUTPUT', 
                       @LoginName = @DB_LoginName OUTPUT

    -- Only proceed if we found a valid login/schema name
    IF @DB_LoginName IS NOT NULL
    BEGIN
        -- Build the main command with the correct database and schema references
        SET @Command = N'declare @StartYear int, @EndYear int, @WgYr int, @RunNo int, @PaySlips int, @YearEnd int
        set @StartYear = 2020
        set @EndYear = 2020
        declare WgRun_Cursor cursor for select WgYr, RunNo from [' + @DB_Name + N'].[' + @DB_LoginName + N'].WgRun where WgYr >= @StartYear and WgYr <= @EndYear order by WgYr, RunNo
        open WgRun_Cursor
        fetch next from WgRun_Cursor into @WgYr, @RunNo
        while @@FETCH_STATUS = 0
        begin
            set @PaySlips = isnull((select count (distinct EmpNo) from [' + @DB_Name + N'].[' + @DB_LoginName + N'].WgPaySlip where WgYr = @WgYr and WgYr <= @EndYear and RunNo = @RunNo),0)
            insert into #PaySlips values(@WgYr, @RunNo, @PaySlips)
            fetch next from WgRun_Cursor into @WgYr, @RunNo
        end
        close WgRun_Cursor
        deallocate WgRun_Cursor

        declare WgYr_Cursor cursor for select WgYr from [' + @DB_Name + N'].[' + @DB_LoginName + N'].WgYr where WgYr >= @StartYear and WgYr <= @EndYear order by WgYr
        open WgYr_Cursor
        fetch next from WgYr_Cursor into @WgYr
        while @@FETCH_STATUS = 0
        begin
            set @PaySlips = isnull((select sum(PaySlips) from #PaySlips where WgYr = @WgYr),0)
            set @YearEnd = isnull((select count (distinct EmpNo) from [' + @DB_Name + N'].[' + @DB_LoginName + N'].WgYearEnd where WageYear = @WgYr),0)
            insert into #TotalNumber values ((select top 1 Nm from [' + @DB_Name + N'].[' + @DB_LoginName + N'].FrmData), @PaySlips, @YearEnd, @WgYr)
            fetch next from WgYr_Cursor into @WgYr
        end
        close WgYr_Cursor
        deallocate WgYr_Cursor'

        -- Execute the main command
        EXEC sp_executesql @Command
    END
    ELSE
    BEGIN
        -- Handle cases where no valid login/schema was found
        PRINT N'Warning: No valid login/schema found for database: ' + @DB_Name
    END

    FETCH NEXT FROM database_cursor INTO @DB_Name
END

-- Cleanup and output results
CLOSE database_cursor
DEALLOCATE database_cursor

select * from #TotalNumber
drop table #PaySlips
drop table #TotalNumber

What Changed & Why #

1. Target Database Context for sys.database_principals:

   • We use USE [@DB_Name] in a dynamic query to switch to the target VLData database before querying sys.database_principals. This ensures we're getting users from the correct database, not the one you're running the script from.
   • We use an OUTPUT parameter with sp_executesql to pass the found login/schema name back to @DB_LoginName.

2. Avoid Multiple Results:

   • Since you mentioned each VLData database has exactly one matching login/schema, the query will return a single value. If there's ever a chance of multiple matches, add TOP 1 to the SELECT statement (e.g., SELECT @LoginName = TOP 1 name ...).

3. Robustness:

   • Added a check for @DB_LoginName IS NOT NULL to skip databases where we can't find the correct schema, preventing invalid object reference errors.
   • Switched from sys.sysdatabases to sys.databases (the modern, recommended system view for SQL Server).

4. Correct Object References:

   • The script now builds references like W2595VLData.showoffstudios.WgPaySlip instead of using the database prefix as the schema.

内容的提问来源于stack exchange，提问作者EspenB