Storing variable values whilst using cursors

[archanapatelwhite]

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 [e-mail]
G5R
(for here i want @allrastypes to be 'G5R,')

Email Address [e-mail]
G1
G3
G5O

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

Can someone help

Thanks
Archana

[Russ Rose]

Set your strings equal to empty strings immediately after declaring them.
Otherwise you are adding a string to a NULL which yields a NULL.

Not sure all you are trying to accomplish, but the following is better for
building comma delimited strings than a cursor.

DECLARE @allrastypes varchar(200)
SELECT @allrastypes = ''

SELECT @allrastypes = @allrastypes + ',' + T.RasType
FROM (SELECT DISTINCT RasType
FROM dbo.tblMaintCase x,
dbo.tblMaintCustomer y
WHERE x.caseid = y.caseid AND
y.Email = @emailid
AND RasType is not null) T

--Drop first comma
SELECT @allrastypes = SUBSTRING(@allrastypes, 2, 200)
<ar***************@googlemail.comwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...

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 [e-mail]
G5R
(for here i want @allrastypes to be 'G5R,')

Email Address [e-mail]
G1
G3
G5O

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

Can someone help

Thanks
Archana