SQL Server - 游標和動態 SQL

這個查詢正在工作，但沒有給我想要的輸出。

當通過列中的行至少一次滿足兩個條件之一時，我期望“失敗”

$$ TVF $$或者$$ RPPS $$. 但是在這裡，我在更新的表格中始終將“OK”作為輸出。通常，我應該為該行設置“失敗”$$ AFUP $$和行的“OK”$$ BICHATP $$. 你能在你的機器上執行它嗎？

drop table [MyTable]
drop table [BICHATP]
drop table [AFUP]


CREATE TABLE [AFUP]
(
   [TVF] nvarchar(10),
   [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
   [TVF] nvarchar(10),
   [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
   ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
   ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
   [Tabs] nvarchar(100),
   [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
   ('AFUP'), ('BICHATP');


DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
   SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
   SET @cmd = 'update [MyTable]
               set [Check_column] = 
                   case when left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'' then ''FAILED''
                        else ''OK''
                   end
              from DB_SANOFI.dbo.' + @Tabs;

   EXEC sp_executesql @cmd;

   FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

表

   AFUP           BICHATP
------------     ------------
M006 | 1010      M000 | 1101
M054 | 1000      M090 | 1001
M015 | 2324      M012 | 1023

預期結果：

Tabs    | Check column
-----------------------
  UFAP | FAIL
BICHATP | OK

如果我正確理解您的問題，我認為您希望OK在連接MyTable表上找不到符合您的FAILED條件的任何行。我正在使用並確保我只更新正在處理的特定表。NOT EXISTS``MyTable

如果我誤解了您的要求，請告訴我。

drop table if exists [MyTable]
drop table if exists [BICHATP]
drop table if exists [AFUP]


CREATE TABLE [AFUP]
(
   [TVF] nvarchar(10),
   [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
   [TVF] nvarchar(10),
   [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
   ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
   ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
   [Tabs] nvarchar(100),
   [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
   ('AFUP'), ('BICHATP');


DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
   SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
   SET @cmd = 'update [MyTable]
               set [Check_column] = case when NOT EXISTS
                   (SELECT * FROM ' + @Tabs + ' where
                   left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')
                   then ''OK''
                        else ''FAILED''
                   end
              from ' + @Tabs + ' WHERE [MyTable].Tabs = ''' + @Tabs + '''';

              print @cmd
   EXEC sp_executesql @cmd;

   FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

---

| Tabs    | Check_column |
|---------|--------------|
| AFUP    | FAILED       |
| BICHATP | OK           |

引用自：https://dba.stackexchange.com/questions/223212