Steven Willis (7/7/2013)

---

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.

 

[server]

j3688802s

sample_web

coalesce(@liststr+',' ,'') + quotename(column_name)

from [server].j3688802s.information_schema.columns where table_name = 'sample_web'

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'coalesce'.