|
|
Posted by Russ Rose on 02/21/07 03:36
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)
<archanapatelwhite@googlemail.com> wrote in message
news:1172022085.781280.317520@v45g2000cwv.googlegroups.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 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]
|