从数据库表中获取要在select语句中使用的名称，而不是databasename

Question

我有一个脚本，它在数据库名称中使用word VLdata运行数据库。由于数据库名和表名不同，所以我在sys login表中查找与表名同名的登录用户。

。例如，数据库名称为W2595VLData，但表名称为showoffstudios.WgYr">

所有数据库中都有一个与数据库表名同名的登录用户

当我运行下面的脚本时，我得到错误信息"子查询返回了多个值">

我希望下面的SELECT @DB_LoginName语句也只能通过使用"VLData">

因为我需要@DB_LoginName作为表名而不是数据库名。因此，当在数据库中查找。wgpayslip列时，它需要例如W2595.showoffstudios.WgPaySlip。

任何帮助或提示将非常感激!

DECLARE database_cursor CURSOR FOR SELECT name FROM sys.sysdatabases where name LIKE '%VLData% 
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0 
BEGIN

SELECT @DB_LoginName = (select name as username
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
and name != 'dbo'

原始脚本:这给了我错误

无效的对象名称'2544VLData.2544.WgRun'。因为数据库名称被更改，表名称有了另一个名称。

DECLARE @DB_LoginName varchar(200) 
DECLARE @Command nvarchar(MAX)
create table #PaySlips
(
WgYr int,
RunNo int,
PaySlips int
);
create table #TotalNumber
(
Firmanavn varchar(100),
Lønnsslipper int,
Sammenstillingsoppgaver int,
Lønnsår int
);

DECLARE database_cursor CURSOR FOR SELECT name FROM sys.sysdatabases where name LIKE '%VLData%'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @DB_LoginName = LEFT(@DB_Name,CHARINDEX('VLData',@DB_Name)-1)
SET @Command = '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 + '].' + '[' + @DB_LoginName + '].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 + '].' + '[' + @DB_LoginName + '].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 + '].' + '[' + @DB_LoginName + '].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 + '].' + '[' + @DB_LoginName + '].WgYearEnd where WageYear = @WgYr),0)
insert into #TotalNumber values ((select top 1 Nm from ' + '[' + @DB_Name + '].' + '[' + @DB_LoginName + '].FrmData), @PaySlips, @YearEnd, @WgYr)
fetch next from WgYr_Cursor into @WgYr
end
close WgYr_Cursor
deallocate WgYr_Cursor'
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name 
END
CLOSE database_cursor 
DEALLOCATE database_cursor
select * from #TotalNumber
drop table #PaySlips
drop table #TotalNumber ```

Answer 1

看起来您正在看到多个登录，或者相同登录的多个实例。

或者像我下面所示的那样添加一个top(1)，或者重写代码来分别处理每个LoginName可能更好。

SELECT @DB_LoginName = (select top(1) name as username
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
and name != 'dbo'