Sql-server – SQL Server – Cursor and Dynamic SQL

cursorsdynamic-sqlsql server

this query is working but not giving me the output I want.

I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But here I have always 'OK' as output in my updated table. Normally, I should have 'FAILED' for the row [AFUP] and 'OK' for the row [BICHATP].

Can you run it on your machine? 

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

Tables

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

Expected result:

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

Best Answer

If I understand your question correctly, I think you want OK in the MyTable if you cannot find any row on the joined tables that meets your FAILED conditions. I'm using NOT EXISTS and also made sure I'm only updating MyTable for the specific table being processed.

Let me know if I have misunderstood your requirements.

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           |
Related Question