erikd (7/7/2013)
I'm just not sure how to do it.When I run:
select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)
I get a list of column names like so:
[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...
Running this returns nothing:
select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)
I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?
Thanks
I think just setting your variable to '' will do the trick.
SET @liststr = ''
select @liststr = 'coalesce...etc
or
(' + ISNULL(@liststr,'') + '+'','' ,'''')
If @liststr is NULL the concatenation will also return NULL.
You might also consider using the FOR XML PATH method for concatentation which is often more efficient than using COALESCE.