July 29, 2016 at 2:11 pm
The query looks similar to:
SELECT STUFF((SELECT ISNULL(field + CHAR(10) + CHAR(10),'')
FROM
FOR XML PATH('')),1,0,'')
It is being used as part of a CROSS APPLY to bring back a list of fields each on a new line. The code strangely enough works fine in SQL 2012 and higher. I also tried replacing ISNULL with COALESCE, but this still did not work for older versions of SQL. If we just use:
SELECT STUFF((SELECT ISNULL(field,'') + CHAR(10) + CHAR(10)
then the characters (line feed symbols) will show for any null fields in the report.
I'm a little stumped here, and thinking I may need to resort to a CASE statement to pull this off. Any ideas are greatly appreciated!
Thank you,
Tom
July 29, 2016 at 2:19 pm
It's working as designed. If you try to concatenate a string with a NULL value, the result will be a NULL value.
Check the following example:
SELECT field,
field + 'x',
ISNULL(field + 'x',''),
ISNULL(field,'') + 'x'
FROM (VALUES ('Test'), (''), (NULL))x(field);
July 29, 2016 at 2:35 pm
Thanks for the quick reply. My problem is not so much with the functionality of ISNULL with a concatenated field. My problem is the syntax I need to use is not supported in SQL 2008 R2 or below. I get the following error messages when trying to use in SQL 2008 R2 and below:
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74775
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74806
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74830
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74837
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74844
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74851
Incorrect syntax near ' '.
July 29, 2016 at 3:01 pm
tskelley (7/29/2016)
Thanks for the quick reply. My problem is not so much with the functionality of ISNULL with a concatenated field. My problem is the syntax I need to use is not supported in SQL 2008 R2 or below. I get the following error messages when trying to use in SQL 2008 R2 and below:Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74775
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74806
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74830
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74837
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74844
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74851
Incorrect syntax near ' '.
Then perhaps you could share the whole query you are working with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2016 at 6:37 am
wow Line 74851
thats a huge proc to debug.
at that size, it must be doing a zillion things, that's rough.
Lowell
July 31, 2016 at 7:53 am
It's actually the SQL from a view being created that is part of a large deployment script.
August 1, 2016 at 6:35 am
tskelley (7/31/2016)
Thank you Inspector Holmes. It's actually the SQL from a view being created that is part of a large deployment script.
You know? Sarcasm isn't a good idea when you're asking help for free to volunteers with no pay.[/strike]
Also, without details there's no way we can know what's going on there. The syntax that you posted is supported by SQL Server 2008. If the code is part of a view, post the view definition (at least). You could also read the articles linked in my signature to get better help.
August 1, 2016 at 7:15 am
Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.
And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.
Thank you all for your help,
Tom
August 1, 2016 at 7:51 am
tskelley (8/1/2016)
Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.
Thank you all for your help,
Tom
It's good to know that you found the solution to your problem.
August 2, 2016 at 12:58 pm
tskelley (8/1/2016)
Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.
Thank you all for your help,
Tom
Tom, thank you for posting that you had solved your problem and explaining what the issue turned out to be. It's good to have that for closure. We've all been through times when we were supremely frustrated over problem issues and the frustration is compounded when its difficult to supply the details that others are asking for. But please understand that everyone answering questions here is doing so on their own time out of the goodness of their hearts. No one (except Steve) is getting paid to do it. No one has a service-level agreement with the people posting questions, but they really do want to help. We're all glad you found your answer. Best of luck in the future.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply