CURSOR PROBLEM

Last Post 16 Aug 2010 09:11 AM by russellb. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
werice
New Member
New Member

--
16 Aug 2010 07:22 AM
I HAVE created a cursor that holds 4 record as below

SELECT DISTINCT site_name 
      from core.org_site_contact osc 
      INNER JOIN core.org_site os 
      ON os.org_site_id = osc.org_site_id 
         WHERE osc.contact_id = @iContactID

The 4 records are 
         'John mannard school'
         'Germentown'
         'Adir'
         'Rowen E.S.'

I have a variable that gets the fetch called @site  and a variable that store the sites @Contact Sites

 OPEN contactsites 
   FETCH NEXT from contactsites into @vSite 
      while @@fetch_status = 0 
      begin 
      SET @vContactSites = @vSite + ','   --This fetch should hold the first site

   fetch next from contactsites into @vSite 
      SET @vContactSites = @vContactSites + @vSite  This fetch shold add and hold the rest of the table names.

END

The issue is, when the cursor is finished it only has the last name 'Rowen E.S.'   it should have
      ''John mannared School,Germentown,Adir,Rowen E.S.'   why dosn't the variable concat all rows?

Does anyone know?
russellb
New Member
New Member

--
16 Aug 2010 09:11 AM
This is overwriting the value with each iteration


OPEN contactsites
FETCH NEXT from contactsites into @vSite
while @@fetch_status = 0
begin
SET @vContactSites = @vSite + ',' --This fetch should hold the first site

fetch next from contactsites into @vSite
SET @vContactSites = @vContactSites + @vSite This fetch shold add and hold the rest of the table names.

END

should be more like this

Declare @vContactSites
Set @vContactSites = ''


OPEN contactsites
FETCH NEXT from contactsites into @vSite
while @@fetch_status = 0
begin
SET @vContactSites = @vContactSites + @vSite + ',' --This fetch should hold the first site

fetch next from contactsites into @vSite

END
You are not authorized to post a reply.

Acceptable Use Policy