November 3, 2008 at 8:15 am
I have the following script which is supposed to get a column of data from a table and build a comma seperated string based upon the result. The data from the table (or stored proc in this case) is abbreviated months e.g. Jan, Feb, Mar etc:
declare @periodArray varchar(1000)
declare @myTable table
(
Period varchar(3)
)
insert into @myTable
exec rpGetActualPeriods
declare @myPeriod varchar(3)
declare actPeriods cursor for
select Period from @myTable
open actPeriods
fetch next from actPeriods into @myPeriod
while @@fetch_status = 0
begin
select @periodArray = @periodArray + @myPeriod + ','
fetch next from actPeriods into @myPeriod
end
select @periodArray
close actPeriods
deallocate actPeriods
When I run the script I get NULL returned. I'm sure this is going to be an easy one to answer!
Any suggestions on an alternative to the cursor also welcome!!
Thanks
David
November 3, 2008 at 8:19 am
Initialize the PeriodArray Variable with blank ('')
Further, why are you using a cursor? I think you could relatively easy refactor into a funtion, which has far better performance that a cursor.
November 3, 2008 at 8:49 am
vivets (11/3/2008)
Initialize the PeriodArray Variable with blank ('')Further, why are you using a cursor? I think you could relatively easy refactor into a funtion, which has far better performance that a cursor.
Hi Vivets, thanks for your help it works as expected now. I'll look into using a function instead. I knew that there would probably be a better method than using a cursor but just where was the issue.
Thanks
David
November 3, 2008 at 9:18 pm
select @periodArray = ISNULL(@periodArray + ',', '') + Period
from @myTable
select @periodArray
Much less typing.
🙂
_____________
Code for TallyGenerator
November 3, 2008 at 9:48 pm
Have to agree with Sergiy on this one.
November 4, 2008 at 12:35 am
Sergiy (11/3/2008)
select @periodArray = ISNULL(@periodArray + ',', '') + Period
from @myTable
select @periodArray
Much less typing.
🙂
Sergiy, amazing! Can you explain how it works though?! In the past I've used ISNULL to simply replace a value if it is null. What I don't understand is how the results from the table were converted into a concatenated string.
Thanks
David
November 4, 2008 at 6:41 am
David (11/4/2008)
Sergiy, amazing! Can you explain how it works though?! In the past I've used ISNULL to simply replace a value if it is null. What I don't understand is how the results from the table were converted into a concatenated string.Thanks
David
When you declare a variable it's initial value is NULL.
NULL used with any operator (such as concatenation, '+') returns NULL.
This both explains why your original cursor solution returned NULL (it just kept concatenating NULL+something and getting NULL) and why Sergiy's solution works (on returning the first row, NULL+',' is NULL so an empty string is used; this is concatenated with period and all subsequent rows return (something+',')+period, i.e. add a comma and the next value to what we already have).
Derek
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply