CODEWITHSUNDEEP
sqlcursor
Thang Lang
Thang Lang

Reputation: 75

SQL - Need some help for this situation without using cursor

Currently, in my stored procedure, I'm using the Cursor for the situation below. What I know is we need to avoid using Cursors as much as we can.

So, I post the situation here to you can help me to solve it.

  1. In table A, I use Cursor to get one-by-one UserID
  2. With each UserID, I call 2 functions to work with business. The result is 2 strings.
  3. These 2 strings will be saved to table B.
  4. If success, do 2 actions: update Status = 1 in table A. And return a message.
  5. If fail, do 1 action: return a message.
  6. Loop Cursor

With business from step 2 - 5, how can we create a stored procedure with using the Cursor?

I hope you can give me advice to solve this.

Upvotes: 0

Views: 174

Answers (2)

Diego
Diego

Reputation: 36146

on a high level you can do something like this (I added a insertDate in your table B because I think it would be necessary to the update)

First, insert data from tableA into tableB:

insert into tableB (ID, column1, column2, insertDate)
select ID, callFunction1(userID), callFunction2(userID), getdate()

then for the records inserted, set status=1

update tableA
set status=1 
where ID in (select ID from tableB where insertDate=today)

now, the return message kind is kinda complicated, you cant return a message per row, if any of them "fail", the whole batch will fail.

what exactly would cause a failure in your case?

Upvotes: 1

nunespascal
nunespascal

Reputation: 17724

Who says you need to avoid using cursors?
Cursors are provided for a reason, and there are some situations where you have to use a cursor or resort to fetching the records and processing the logic in some programming language.

If you want to have a per record success or failure, within the database even inside a stored procedure, you will need a cursor. (yes there are insert after triggers but that would just complicate your logic)

Upvotes: 1

Related Questions