How to execute a Begin-End Transact SQL statements in Apache nifi?

Question

I am trying to execute an SQL statement with Begin - End and Cursor in between using Execute SQL processor in Apache Nifi. But this is throwing an error telling "Unable to execute SQL select query". How can I execute transact SQL statements in nifi? Which processor is best suited for this?

Below is the SQL code snippet that I am trying to execute,

DECLARE @Cursor CURSOR
DECLARE @stlsn binary(10), @endlsn binary(10), @sequal binary(10), @op char, @upm varbinary(128), @rn numeric, @tg char(10), @ti char(10), @ln char(10), @dv char(10), @cid int

BEGIN
    SET @Cursor = CURSOR FOR
    SELECT * FROM cdc.dbo_SampleDB_CT  

    OPEN @Cursor 
    FETCH NEXT FROM @Cursor INTO @stlsn, @endlsn, @sequal, @op, @upm, @rn, @tg, @ti, @ln, @dv, @cid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @op, @rn, @tg, @ti, @ln, @dv
        IF @op= 1 Execute ('DELETE FROM dbo.NewDB WHERE RNo = '+@rn)
        IF @op= 2 Execute ('INSERT INTO dbo.NewDB VALUES ('+@rn+','''+@tg+''','''+@ti+''','''+@ln+''','''+@dv+''')')

      FETCH NEXT FROM @Cursor INTO @stlsn, @endlsn, @sequal, @op, @upm, @rn, @tg, @ti, @ln, @dv, @cid
    END;

    CLOSE @Cursor ;
    DEALLOCATE @Cursor;
END;

Better question is why would you want to do this at all? You've managed to roll together a cursor (an updatable one and has `select *` to boot) and dynamic SQL for no apparent reason. These are both the worst things to do in SQL. This could just be two **static** statements: a joined `delete` and an `insert...select...`, or perhaps just an update. What are your table definitions and what are you trying to achieve? — Charlieface, Mar 04 '21 at 14:34
@Charlieface I am trying to utilize the CDC feature of MSSQL, basically to monitor the changes in source DB and update them to the target DB. Here I am using the cursor to read every row of the _CT table of CDC and based on the `@op` value, I am using dynamic SQL to update target DB. Please let me know if there are better ways to achieve this. Thanks. — Rakshitha Vasishta, Mar 04 '21 at 14:45
What is your table definitions of the `dbo_SampleDB` and `NewDB` — Charlieface, Mar 04 '21 at 14:55
Let's not forget the fact the OP is **injecting** their "parameters" as well, instead of doing what they should be doing an **parametrising** them. — Thom A, Mar 04 '21 at 15:26
Please test that in SSMS before posting. You've failed to declare a bunch of local variables. — David Browne - Microsoft, Mar 04 '21 at 15:35
@DavidBrowne-Microsoft I have edited the question to include variable declarations. — Rakshitha Vasishta, Mar 04 '21 at 17:36

score 1 · Accepted Answer · answered Mar 04 '21 at 15:06

This doesn't need to be dynamic at all. You can just do a joined DELETE and an INSERT...SELECT....

You haven't specified your column names so I'm guessing a bit here.

DELETE FROM n
FROM dbo.NewDB n
JOIN cdc.dbo_SampleDB_CT c ON c.RNo = n.RNo
WHERE c.__$operation = 1;

INSERT INTO dbo.NewDB
SELECT c.RNo,c.tg,c.ti,c.ln,c.dv
FROM cdc.dbo_SampleDB_CT c
WHERE  c.__$operation = 2;

I note that you haven't dealt with CDC update rows.

How to execute a Begin-End Transact SQL statements in Apache nifi?

1 Answers1