1

I have a stored procedure in which I use a cursor to parse arrays separated by semi-colons (eg Marketing;Diligence;Deal Flow) is returned in three separate rows with each value in its own row. The procedure/cursor works perfectly when run in a fresh session. However if I re-run the procedure within the same session, the cursor returns 0 rows. 

DECLARE @category NVARCHAR(MAX)

DECLARE cursor_product CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
     SELECT DISTINCT category AS val 
     FROM #temp;

OPEN cursor_product;

CREATE TABLE #temp5
(
     Name varchar(max)
)

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #temp5
    EXEC sp_ParseArray @category, ';'

    FETCH NEXT FROM cursor_product INTO @category
END;

CLOSE cursor_product;
DEALLOCATE cursor_product;
Connor Herring
  • 19
  • 2
  • 1
    An alternative to `#temp`, consider use [table variables](https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-variables/). – Marco Aurelio Fernandez Reyes Feb 20 '20 at 22:52
  • You haven't defined `sp_ParseArray`, but I think a cursor is not a great way to do this. My general advice: avoid cursors whenever possible. Have a look at [this question](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) which is similar but splits comma separated values. – pcdev Feb 20 '20 at 23:33
  • More on why cursors are often considered "bad", and some alternatives: https://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server – pcdev Feb 20 '20 at 23:36
  • And with that out of the way, now for a comment on your actual code: notice that your first `FETCH NEXT` statement happens after you first execute the `sp_ParseArray` method, ie. the first time this executes, `@category` will be null. This may be affecting your output – pcdev Feb 20 '20 at 23:40
  • 2
    Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Feb 21 '20 at 05:13

0 Answers0