Re-assigning a Cursors SELECT Statement. SQL Server 2008 -

for while have been searching resources way re-assign cursors select statement no success.

first let me show code far:

@keystring - stores list of id's seperated comma.
@individual - stores individual id once @keystring 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         select blah tbl_blah id = @individual     open id_cursor   end   else   begin     /*reset id_cursor = select blah tbl_blah keyword = @individual      (the next @individual after first loop)*/   end    fetch next id_cursor @blah   while @@fetch_status = 0   begin        ......       fetch next id_cursor @blah   end /*loops 'while len(@keystring) > 1*/ end 

somehow need assign id_cursor new select statement. id_cursor needs use next @individual returned once has looped.

there several ways have thought possible:

1. how shown in code:

               if not id_cursor exist             create id_cursor             else             change id cursor             end 

2. declare id_cursor outside loop , replace whole if statement ('if not cursor_status('global, 'id_cursor')>= -1) with:

               set cursor select blah tbl_blah keyword = @individual 

3. or somehow delete id_cursor when 'loops 'while len(@keystring) > 1' , declare each time round?

the trouble have yet find , documentation/syntax regarding re-assigning cursors , executing them again. have clear 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 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 exists 1 connection

example row: 1 | 2

my project error log system, when using tags on stack overflow, users assign error keywords relate problem make quick , easy make search related issues in future.

this particular call database search based on these keywords. example: user select ‘emails’ , ‘outlook’ in vb , @keystring populated ‘4, 6’.

in example there 2 keywords in @keywords first stage break them individual keyword_id’s. @individual used for. example @individual = 4 (for first loop). once have individual id need run ‘select id tbl_main keyword_id ‘%’ + @individual + ‘%’;’ each id returned select statement need check , see if id present in temtable. if doesn’t exist need insert temp_table (@individual (because id tbl_main), 1 (because first instance of id in temp_table)). example: @individual = 4 , error id = 1 in tbl_main contains ‘4’ in keyword_id in temp_table id 1 , count 1.

on second loop @individual equal 6 (second id user searching for), because error id = 1 exists in temp_table , contains ‘6’ in keyword_id, temp_table need updated not inserted (update keyword_count id = @individual) once done particular row (1 | 2) (1 because it’s id | 2 because contains 2 of ids selected user) once individual errors have been searched return temp_table ordered keyword_count desc vb , populate list view data. means error searching appear 1st in list countains of keywords searched for.

so thats overall aim: return row in tbl_main contains keyword_id’s searched for, ordered largest first.

thanks reply. so. forget sp. need if understood problem:

 declare @keystrings varchar(max)
 set @keystrings='4, 6' --your example set @keystrings=replace(@keystrings, ' ', '') --remove spaces, don't need them set @keystrings=concat('select ', replace(@keystrings, ',', ' union select '))
 select tbm.id, count(tbm.id) 'hitcount' tbl_main tbm tbm.k_id in (@keystrings) group tbm.id 

you can try in immediate window (ssms 'new query' window). let me know if it's not right , explain why / other results need