Posted by archanapatelwhite on 02/21/07 01:41

Hi below is the code I am using.

------------------------------------
SET NOCOUNT ON

DECLARE @emailid varchar(50), @rastype varchar(50),
@message varchar(80)
declare @allrastypes varchar(200)

DECLARE email_cursor CURSOR FOR
SELECT distinct Email
FROM dbo.tblMaintCustomer
ORDER BY Email

OPEN email_cursor

FETCH NEXT FROM email_cursor
INTO @emailid

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = 'Email Address ' +
@emailid

PRINT @message

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

DECLARE rastype_cursor CURSOR FOR
SELECT distinct [RasType]
FROM dbo.tblMaintCase x, dbo.tblMaintCustomer y
WHERE x.caseid = y.caseid AND
y.Email = @emailid
and RasType is not null

OPEN rastype_cursor
FETCH NEXT FROM rastype_cursor INTO @rastype
select @allrastypes = @allrastypes + ',' + @rastype

IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = @rastype
PRINT @message
select @allrastypes = @allrastypes + ',' + @rastype
FETCH NEXT FROM rastype_cursor INTO @rastype

END

CLOSE rastype_cursor
DEALLOCATE rastype_cursor

insert into dbo.tblTest values(@emailid,@allrastypes)
select @allrastypes = ''
FETCH NEXT FROM email_cursor
INTO @emailid
END
CLOSE email_cursor
DEALLOCATE email_cursor
--------------------------------------

I basically want the value of @allrastypes to accumulate each time it
loops through, which is is not doing.

The result I get is :

Email Address xx@xx.NET
G5R
(for here i want @allrastypes to be 'G5R,')

Email Address yy@yY.ORG
G1
G3
G5O

(for here i want @allrastypes to be 'G1,G3,G5O')

Can someone help

Thanks
Archana

• Next in forum: Re: Is there a Query Analyzer
• Prev in forum: Subquery and display help
• Thread view: Storing variable values whilst using cursors

[Reply to this message]