SQL Cursor problem

I am getting the following errors but can't figure out why..

Msg 16915, Level 16, State 1, Procedure client_myClientsProc, Line 46

A cursor with the name 'cur_keywords' already exists.

Msg 16905, Level 16, State 1, Procedure client_myClientsProc, Line 47

The cursor is already open.

And then if I try to run it again it says

Msg 208, Level 16, State 0, Procedure client_myClientsProc Line 49

Inv开发者_运维百科alid object name '##CLIENTS_KEYWORD.

Now this is old code that I am trying to fix, so please bear with me...

ALTER PROCEDURE [dbo].[client_myclientsproc]  
   @Keywords varchar(max),  
   @Delimiter varchar(10) = ' '  
AS  
BEGIN  
  SET NOCOUNT ON;  

  DECLARE @MYQUERY NVARCHAR(MAX);    
  DECLARE @tempkeyword varchar(4000)    
  DECLARE @TempCount INT   

  IF OBJECT_ID('TempDB..##CLIENTS_KEYWORD') IS NOT NULL
  BEGIN
     DROP TABLE ##CLIENTS_KEYWORD
  END
  ELSE
  BEGIN
    CREATE TABLE ##CLIENTS_KEYWORD(client_id int)  
  END

  IF OBJECT_ID('TempDB..##TEMP_CLIENTS_KEYWORD') IS NOT NULL
  BEGIN
    DROP TABLE ##TEMP_CLIENTS_KEYWORD
  END
  ELSE
  BEGIN
     CREATE TABLE ##TEMP_CLIENTS_KEYWORD(productid int)
  END  

  SET @MYQUERY = 'SELECT clientID, Client_Name FROM MYCLIENTS WHERE  ClientID IN ';                    

 IF(@Delimiter<>'none')
 BEGIN   
  DECLARE cur_keywords CURSOR FOR  
    select value from SC_Split(@Keywords,@Delimiter)     

  OPEN cur_keywords  

  FETCH NEXT FROM cur_keywords into @tempkeyword  
  INSERT ##CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @tempkeyword  

   WHILE @@FETCH_STATUS = 0     

    FETCH NEXT FROM cur_keywords into @tempkeyword  

     INSERT ##TEMP_CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @tempkeyword 
          select @TempCount=COUNT(client_id) from  ##TEMP_CLIENTS_KEYWORD     
            IF(@TempCount<>0)    
                BEGIN    
                    DELETE FROM ##CLIENTS_KEYWORD WHERE client_id NOT IN(SELECT client_id from ##TEMP_CLIENTS_KEYWORD)     
       INSERT  ##CLIENTS_KEYWORD (client_id) (select client_id from ##TEMP_CLIENTS_KEYWORD)                       
                END  
       CLOSE cur_keywords                              
       DEALLOCATE cur_keywords                                  
 END
 ELSE 
 BEGIN  
   print(@Keywords)
   INSERT ##CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @Keywords      
 END  

 SET @MYQUERY = @MYQUERY + '(SELECT * FROM ##CLIENTS_KEYWORD)'                           
 SET @MYQUERY = @MYQUERY + ' ORDER BY NAME'                    

 print    @MYQUERY   

 EXEC SP_EXECUTESQL @MYQUERY                                           
END  
GO

====================

get clients by keyword code

CREATE PROCEDURE [dbo].[getClientsByKeyword]  
 @Keyword varchar(max)  

AS  
BEGIN  
SET NOCOUNT ON;  


 select                     
  DISTINCT(clients.clientID)
 from                     
  Clients_Table clients                               
  left join clientNumber cn on cn.clientid=clients.clientid                    
 where                     
  clients.activeind = 1                     
  and (clients.Name like '%' + @Keyword + '%'                     
  or clients.clientNum LIKE '%' + @Keyword + '%'           
  or cn.clientN like   '%' + @Keyword + '%' )      

END  



GO

---

• Your OPEN, FETCH, WHILE, CLOSE and DEALLOCATE are misplaced.
• Your temp tables need creating every time.

Try this refactored script. Modify as you need:

DECLARE @tempkeyword varchar(4000)    
DECLARE @TempCount INT   

IF OBJECT_ID('TempDB..##CLIENTS_KEYWORD') IS NOT NULL
    DROP TABLE ##CLIENTS_KEYWORD

CREATE TABLE ##CLIENTS_KEYWORD(client_id int)  

IF OBJECT_ID('TempDB..##TEMP_CLIENTS_KEYWORD') IS NOT NULL
    DROP TABLE ##TEMP_CLIENTS_KEYWORD

CREATE TABLE ##TEMP_CLIENTS_KEYWORD(productid int)

SET @MYQUERY = 'SELECT clientID, Client_Name FROM MYCLIENTS WHERE  ClientID IN ';                    

IF(@Delimiter<>'none')
BEGIN       
     DECLARE cur_keywords CURSOR FOR  
        SELECT value FROM SC_Split(@Keywords,@Delimiter)     

      OPEN cur_keywords  
      FETCH NEXT FROM cur_keywords into @tempkeyword  
      WHILE @@FETCH_STATUS = 0     
      BEGIN
         FETCH NEXT FROM cur_keywords into @tempkeyword  

         INSERT ##CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @tempkeyword               
         INSERT ##TEMP_CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @tempkeyword 
         SELECT @TempCount=COUNT(client_id) from  ##TEMP_CLIENTS_KEYWORD     
         IF(@TempCount<>0)    
         BEGIN    
              DELETE FROM ##CLIENTS_KEYWORD 
                  WHERE client_id NOT IN(SELECT client_id from ##TEMP_CLIENTS_KEYWORD);

                  INSERT  ##CLIENTS_KEYWORD (client_id) 
                  SELECT  client_id from ##TEMP_CLIENTS_KEYWORD;
         END  
      END

     CLOSE cur_keywords                              
     DEALLOCATE cur_keywords                                  
END
ELSE 
BEGIN  
   print(@Keywords)
   INSERT ##CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @Keywords      
END  

SELECT @MYQUERY=@MYQUERY + '(SELECT * FROM ##CLIENTS_KEYWORD) ORDER BY NAME'                           
print    @MYQUERY   
EXEC SP_EXECUTESQL @MYQUERY                                           
END  

---

Did the stored procedure error out previously? It looks like the CLOSE and DEALLOCATE statements were not run, which could happen if the code hit an error since there is no TRY..CATCH in your code or other method to make sure that the cursor has been properly cleared out. Try disconnecting (or manually running the CLOSE and DEALLOCATE in the same connection) and running it again.

The second error is probably for similar reasons - poor clean-up. When you enter the stored procedure the code DROPs the temporary table if it already exists. The CREATE is in the ELSE portion of that statement though. That means that if the table already exists then it will be DROPped and never recreated.

---

Without setting up test tables this end I'm not really sure what your code is doing but I think you can probably avoid use of a cursor at all with something based on the below.

;WITH C
     AS (select clients.clientID,
                clients.Name,
                clients.clientNum,
                cn.clientN
         from   Clients_Table clients
                left join clientNumber cn
                  on cn.clientid = clients.clientid
                     AND clients.activeind = 1)
select value,
       clients.clientID
from   SC_Split(@Keywords, @Delimiter)
       JOIN C
         ON ( C.Name like '%' + value + '%'
               or C.clientNum LIKE '%' + value + '%'
               or C.clientN like '%' + value + '%' )