Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-12 : 08:58:47
|
| Charles writes "Hi folks, I'm writing cursor code (don't worry, i'll explain) to enumerate some unique dates in a table and to amalgamate these unique dates into a list that will form a column list so that I can run a cross-tab on the table... here's a simplified version (without the CREATE TABLE syntax that will only obscure what I'm doing, I've just got the cursor enumerating each date and I'm trying to put each date successively into a string variable, if I can do that, I can make the string anything I want, i.e a properly syntaxed CREATE TABLE statement) alter PROCEDURE testCursor as DECLARE @DateField varchar(8) DECLARE @sqlStmt varchar(2000) DECLARE GetDateFields CURSOR FAST_FORWARD FOR select top 12 ExtractDateFormat from sourcedata group by year(extractdate),month(extractdate), ExtractDateFormat order by 1 desc OPEN GetDateFields FETCH GetDateFields INTO @DateField --prime the cursor WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Field is: '+ @DateField SET @sqlStmt = @sqlStmt + @DateField + ',' FETCH GetDateFields INTO @DateField PRINT @sqlStmt END CLOSE GetDateFields DEALLOCATE GetDateFields go trouble is @SqlStmt is always NULL, it never becomes a comma separated list of fields I'm baffled (i've debugged it in query analyzer) and that shows that the assignment Set @sqlStmt = @sqlStmt + @dateField has no effect whatsoever any ideas? many thanks in advance for any help Regards Charles" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-12 : 09:02:42
|
| You are never setting an initial value for @SQlStmt, which means it is NULL, and appending anything to NULL results in NULL. Start off with a SET @SQLStmt = ''statement to initialize it.In addition, note that you do not need a cursor. the following is valid and produces the same results much quicker and shorter:declare @s varchar(1000)set @s= ''select @s = @s + ', ' + SomeColumn from SomeTableSearch the articles/forums here for "CSV" and you see lots of great examples and techniques. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
why is my string variable not getting updated?