Re-assigning a Cursors SELECT Statement. SQL Server 2008

For a while I have been searching my resources for a way to re-assign a Cursors SELECT statement with no success.

First let me show you my code so far:

@KeyString - stores list of ID's seperated by a comma.
@Individual - stores the individual ID once @KeyString is broken up.

DECLARE @Keystring VarChar(100) = '4, 6'
DECLARE @Individual VarChar(10)

WHILE LEN(@KeyString) > 1
BEGIN

  IF PATINDEX('%, %', @KeyString) > 1
  BEGIN
    SET @Individual = SUBSTRING(@KeyString, 0, (PATINDEX('%, %', @KeyString)))
  END
  ELSE 
  BEGIN
    SET @Individual = @KeyString
  END

  IF NOT CURSOR_STATUS('global', 'ID_Cursor')>= -1
  BEGIN
    DECLARE ID_Cursor Cursor
    FOR
    SELECT Blah FROM tbl_Blah WHERE ID = @Individual
    OPEN ID_Cursor
  END
  ELSE
  BEGIN
    /*RESET ID_Cursor = SELECT Blah FROM tbl_Blah WHERE Keyword = @Individual 
    (The Next @Individual after first loop)*/
  END

  FETCH NEXT FROM ID_Cursor INTO @blah
  WHILE @@FETCH_STATUS = 0
  BEGIN

      ......
      FETCH NEXT FROM ID_Cursor INTO @blah
  END
/*Loops Back to 'While LEN(@KeyString) > 1*/
END

Somehow I need to assign ID_Cursor a new SELECT statement. ID_Cursor needs to use the next @Individual returned once it has looped.

There are several ways I would have thought this was possible:

How its shown in the code:

            IF NOT ID_Cursor already exist Then
            Create ID_Cursor
            Else
            Change ID Cursor
            End

Declare ID_Cursor outside the loop and replace the whole IF Statement ('IF NOT CURSOR_STATUS('global, 'ID_Cursor')>= -1) with:
```
            SET CURSOR FOR SELECT Blah FROM tbl_Blah WHERE Keyword = @Individual
```
Or somehow delete ID_Cursor when it 'Loops Back to 'While LEN(@KeyString) > 1' and declare it each time round?

The trouble is I have yet to find and documentation/syntax regarding re-assigning cursors and executing them again. Do I have to clear the Cursor data first before re-assigning?

Table Structures:

Tbl_Main (ID int, Error_Title varchar, Error_Description varchar, K_ID varchar)

Example row: 1 | Emails not Sending | Please contact your administrator | 4, 6

Tbl_Keyword(ID int, Keyword varchar) Example rows:

4 | Emails

6 | Outlook

Temp_Table(Main_ID int, Keyword_Count int) local temporary table only exists for that one connection

Example row: 1 | 2

My project is an Error Log System, much like when using tags on Stack Overflow, users assign an Error keywords that relate to their problem to make it quick and easy to make a search for related issues in the future.

This particular call to the database is a search based on these keywords. For example: A user will select ‘Emails’ and ‘Outlook’ in VB and then @keystring will be populated with ‘4, 6’.

In this example as there are 2 keywords in @keywords the first stage would be to break them up into individual Keyword_ID’s. This is what @Individual is used for. For this example @individual = 4 (for the first loop). Once I have the individual ID I will then need to run ‘SELECT ID FROM tbl_Main WHERE Keyword_ID Like ‘%’ + @Individual + ‘%’;’ For each ID returned by this SELECT Statement I will need to check and see if the ID is already present in my temtable. If it doesn’t already exist I will need to INSERT INTO temp_table (@individual (because this is the ID from tbl_main), 1 (because this is the first instance of this ID in temp_table)). For example: @individual = 4 and the Error where ID = 1 in tbl_main contains ‘4’ in Keyword_ID so in temp_table the ID would be 1 and the count would be 1.

On the second loop @individual will then equal 6 (second ID that user is searching for), so because the Error where ID = 1 already exists in my temp_table and it contains ‘6’ in its Keyword_ID, temp_table will need to be updated not Inserted Into (UPDATE Keyword_Count WHERE ID = @Individual) once this is done this particular row will look like (1 | 2) (1 because it’s the ID | 2 because it contains 2 of the Ids selected by the user) Finally once all individual errors have been searched for I will return temp_table ordered by Keyword_Count DESC to VB and populate a list view with this data. This means that the most likely Error you are searching for will appear 1st in the list as it countains most of the keywords searched for.

So thats my overall aim: to return the row in tbl_main which contains the most keyword_ID’s searched for, ordered by largest first.

Solution

Thanks for the reply. So. Forget your SP. This is what you need if I understood the problem:


DECLARE @keystrings varchar(max)

SET @keystrings='4, 6' --your example
SET @keystrings=REPLACE(@keystrings, ' ', '') --remove spaces, we don't need them
SET @keystrings=CONCAT('SELECT ', REPLACE(@keystrings, ',', ' UNION ALL SELECT '))

SELECT TBM.ID, count(TBM.ID) AS 'HitCount'
FROM Tbl_Main AS TBM
WHERE TBM.K_ID IN (@keystrings)
GROUP BY TBM.ID

You can try this in an immediate window (SSMS 'New Query' window). Let me know if it's not right and explain why / what other results you need