SQL Server Exec multiple statements in a cursor based on two list arrays [closed]

wz1wpwve 于 2024-01-05 发布在其他

关注(0)|答案(1)|浏览(108)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 12 days ago.
Improve this question

I've recently transitioned from Oracle to Microsoft SQL Server and I'm struggling with how to execute multiple statements based on a set of arrays. For instance I have a series of record ID's and another list of values I want to update a column to and perhaps perform an additional action on.

BEGIN
    DECLARE List1 ['exampl1', 'example2', 'example3']
    DECLARE List2 ['value1', 'value2', 'value3']
    DECLARE rcount AS int = 1
    OPEN cursor c_whatever
    UPDATE tableA 
    SET colA = "List2.rcount" 
    WHERE id = "list1.rcount"
    DELETE FROM tableB 
    WHERE id = "list1.rcount" AND columnA = "List2.rcount" 
   
    rcount = +1
    CLOSE CURSOR c_Whatever 
END

Super rudimentary (and not syntactically correct) but I think you'll get the gist of what I'm trying to accomplish.

sql-server

来源：https://stackoverflow.com/questions/77708703/exec-multiple-statements-in-a-cursor-based-on-two-list-arrays

1条答案

按热度按时间

1u4esq0p1#

SQL Server does not have arrays, but it does have temporary tables and table variables that can be used similarly.

One equivalent of your code in SQL Server would be:

DECLARE @List TABLE (
    Col1 VARCHAR(100),
    Col2 VARCHAR(100)
);
INSERT @List
VALUES
    ('exampl1', 'value1'),
    ('example2', 'value2'),
    ('example3', 'value3');
UPDATE A 
SET colA = L.Col2
FROM @List L
JOIN tableA A
    ON A.id = L.Col1;
DELETE B
FROM @List L
JOIN tableB B
    ON B.id = L.COl1
    AND B.columnA = L.COl2;

Here I have combined the two arrays into a single table variable having two columns. That table variable can be used in SELECT, INSERT, UPDATE, and DELETE statements just like any other table, so no cursor is needed.

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server Exec multiple statements in a cursor based on two list arrays [closed]

1条答案

相关问题

热门标签

最新问答