Select 2 columns into a Cursor

[Iamthestig]

Hi everyone,

Here is my function:

DECLARE @CursRef integer
DECLARE @MeetText varchar(1000)
DECLARE @MeetName varchar (51)
DECLARE @MeetDate char(11)

DECLARE CursorMeet CURSOR FOR
SELECT CONTACTCREF from tblMeetingDetails WHERE CLID=@CLID and CREF=@CREF AND DETAILTYPE='MEET'
ORDER BY ContactDate DESC

OPEN CursorMeet

FETCH NEXT FROM CursorMeet INTO @CursRef

SELECT @MeetText=''

WHILE (@@FETCH_STATUS <> -1)
BEGIN

SELECT @MeetName=(SELECT Firstname + ' ' + LastName from tblContacts where CREF=@Cursref)
SELECT @MeetDate=(SELECT ContactDate from tblMeetingDetails where ContactCREF=@Cursref and CLID=@CLID and CREF=@CREF AND DETAILTYPE='MEET')
IF @MeetDate Is Not Null
SELECT @MeetText=@MeetText + @MeetName + '(' + @MeetDate + ')' + ','
ELSE
SELECT @MeetText=@MeetText + @MeetName + ','

FETCH NEXT FROM CursorMeet INTO @CursRef
END

CLOSE CursorMeet

DEALLOCATE CursorMeet

IF DATALENGTH(@MeetText) > 1
SELECT @MeetText=(SELECT SUBSTRING(@MeetText,1,DATALENGTH(@MeetText)-1) + '.')

RETURN @MeetText


When I declare cursormeet and select contactcref, is it possible to Select a second column? So I add MeetingID to the Select:

DECLARE CursorMeet CURSOR FOR
SELECT CONTACTCREF, MeetingID from tblMeetingDetails WHERE CLID=@CLID and CREF=@CREF AND DETAILTYPE='MEET'
ORDER BY ContactDate DESC

OPEN CursorMeet

FETCH NEXT FROM CursorMeet INTO @CursRef

The code:
SELECT @MeetDate=(SELECT ContactDate from tblMeetingDetails a where a.ContactCREF=@Cursref and a.CLID=@CLID and a.CREF=@CREF AND a.DETAILTYPE='MEET')

should really be

SELECT @MeetDate=(SELECT ContactDate from tblMeetingDetails where MeetingID = @CursRef(2) AND DETAILTYPE='MEET')


I hope this is clear.

Thanks for your help.

 

[bborissov]

DECLARE @MeetId int
DECLARE CursorMeet CURSOR FOR
        SELECT CONTACTCREF, MeetingID
               from tblMeetingDetails
        WHERE CLID=@CLID and CREF=@CREF AND
              DETAILTYPE='MEET'
        ORDER BY ContactDate DESC

    OPEN CursorMeet


FETCH NEXT FROM CursorMeet INTO @CursRef, @MeetId

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Iamthestig]

Ah... Thanks Borislav, you are a star

so easy when you know how!!