CODEWITHSUNDEEP
sqlsql-servert-sql
David Smith
David Smith

Reputation: 91

Stored Procedure using a table valued function and cursor. Getting cursor doesn't exist error

I getting the following error when running a stored procedure. The stored procedure calls a function that declares a cursor for a table using a function that returns a table value function. Please note I didn't include the declaration section of the code 

declare cust_trn cursor local for SELECT * FROM [dbo].[Q03_Create_Customer_Trn](@COUNTERPARTY)

IF CURSOR_STATUS('local','cust_trn') <>-1 
BEGIN
    OPEN cust_trn
END

FETCH NEXT FROM cust_trn  INTO @trn,@cust_name;

WHILE @@FETCH_STATUS=0
  BEGIN    
      IF (@LIST1 IS NULL) BEGIN
          SET @LIST1= @cust_name;
      END
      ELSE BEGIN
          SET @LIST1=ISNULL(@LIST1, '') + ';' + ISNULL(@cust_name, '');
      END 
   FETCH NEXT FROM cust_trn INTO @trn,@cust_name; END   END ELSE BEGIN  SET @LIST1 = 'N/A'; END CLOSE cust_trn ; DEALLOCATE  cust_trn; RETURN ISNULL(@LIST1,'N/A'); END; ISNULL(@LIST1,'N/A'); END;

The error is A cursor with the name 'cursor_name' does not exist the function is begin called as dbo.Q03_get_list_of_counterparties(COUNTERPARTY, 'Y') CParties in the stored procedure.

Upvotes: 0

Views: 218

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

You entire cursor logic could be rewritten to something along these lines.

declare @List varchar(500) = '' --use whatever size is relevant.

select @List = @List + cust_name + ';'
from [dbo].[Q03_Create_Customer_Trn](@COUNTERPARTY)

select @List = left(@List, len(@List) - 1) --removes the last comma

Another option would be to use STUFF and FOR XML. There are hundreds and hundreds of examples of this all over the internet.

Upvotes: 1

Related Questions