|
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
[Back to original message]
|