May 11, 2011 at 9:05 am
Looks this up in books online.
SET CONCAT_NULL_YIELDS_NULL
Also the ISNULL / NULLIF functions could be handy here.
May 11, 2011 at 9:29 am
ISNULL solved my problem.
SET @sql = 'SELECT ''' + STUFF((
SELECT '+'';' + name + ':''+CONVERT(VARCHAR(100), ISNULL(' + name + ',-1))'
FROM sys.columns
WHERE object_id = OBJECT_ID('boss..dealpending','U')
ORDER BY column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +
' FROM dealpending where dealid = 1483436';
Thanks a lot!
May 11, 2011 at 9:32 am
Actually I would put the name in brakets as well. In my system half the columns use spaces and other funny characters. This is most likely why I couldn't run your script.
May 11, 2011 at 9:45 am
Ninja's_RGR'us (5/11/2011)
Actually I would put the name in brakets as well. In my system half the columns use spaces and other funny characters. This is most likely why I couldn't run your script.
ok, I'll do that. Thanks.
Btw, When I add
SET CONCAT_NULL_YIELDS_NULL OFF;
for static script, it works. But when I run it with dynamic query I got:
SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
It's weird. Anyway, ISNULL solves my problem and I can proceed. Thank you again 🙂
May 11, 2011 at 10:01 am
The set option is not the correct solution for this problem. But it may in the future and I thought you needed to be aware of it.
ISNULL was definitely the way to go here.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply