-1

The SELECT statement to populate my Sql Server CURSOR can (often) return zero rows.

Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ?

Or will the RDMS automatically know not to create the CURSOR and/or does an IF based on the exact same SELECT infer the query processing twice?

7
  • The best approach would be to avoid cursors entirely. SQL is an iterative language and really does not do row by agonizing row processing well at all.
    – Sean Lange
    Commented Aug 31, 2023 at 15:42
  • Re: Cursor avoidance dba.stackexchange.com/questions/330338/… -- When I do a set based insert the cashbalance field updates based on a SELECT that is reused by the RDMS. Hence a cashbalance field that say started at $0 might be subtracted by one and thus have $-1 in the first inserted row. If then in the second inserted row +1 is added, because both inserts reference the initial $0 SELECTED value the second row inserts a cashbalance of $1 (i.e. not 0-1+1=0 as it should be). But I'm admittedly far from experienced in SQL or architecture.
    – M S
    Commented Aug 31, 2023 at 21:28
  • @MS: I'm sorry, but I have no idea what you're saying. Please edit your question with an example of a few rows of data, the query you're running, the results you're getting, and what you'd like to get instead. Commented Sep 13, 2023 at 15:22
  • @JonofAllTrades I don't mean to be insolent but J.D. understood and answered the question immediately. After which I marked his response as the answer, thanked him and moved on. Fourteen days later you critique, close and downvote the post. I'm pretty newb but is there some etiquette I am missing?
    – M S
    Commented Sep 14, 2023 at 22:49
  • Good! I can only speak for myself in saying that you're question seemed very vague; while it's great that you did get an answer, you should probably include more details, such as sample rows, in the future. Commented Sep 21, 2023 at 13:36

1 Answer 1

1

Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ?

If the source dataset for the cursor contains 0 rows, then these steps of the cursor are very minimal and negligible. I wouldn't worry about coding an IF statement for it.

does an IF based on the exact same SELECT infer the query processing twice?

Yes, that'll cause the SELECT query to run twice, which depending on the query, can be measurably resource intensive and potentially lock the underlying tables twice as much. So you are better off not doing this.

If you were to do this, you'd be better off running the SELECT query once and storing the results in a temp table. That way you can re-use the results from the temp table later on, if there were any. But as mentioned earlier, I think an IF statement is unnecessary anyway.

1
  • 1
    Thank you, that is precisely the information I was looking for.
    – M S
    Commented Aug 31, 2023 at 20:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.