December 29, 2014 at 8:50 am
Ahh, sorry. Fixed it
December 29, 2014 at 10:07 am
david.gugg (12/29/2014)
I'm getting to this one a day late, but as written it now returns NULL:
DECLARE @OUTPUT VARCHAR(max);SELECT @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
From INFORMATION_SCHEMA.ROUTINES
Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
AND ROUTINE_SCHEMA <> 'tSQLt'
Order By SPECIFIC_SCHEMA,SPECIFIC_NAME
Quick point, the variable is initialized with the value of NULL, any concatenation will therefore yeald NULL, to prevent this initialize the variable with the value of an empty string.
😎
December 29, 2014 at 2:44 pm
Hugo Kornelis (12/26/2014)
I picked the least incorrect answer and got my points. But the answer is still wrong. The more correct answer would be that it finds all stored procedures that start with sp_ but do not have the letter sequence "diagram" anywhere in their name and are not defined in the tSQLt schema.
Very true.
And the really correct answer would add that the code uses a string concatenation method that is undocumented, unsupported, and known to be unreliable. This code should be rewritten using the FOR XML method of string concatenation.
Personally, I regard the infliction of XML on SQL as a disaster, a surrender to the idiots who think that misuse of XML to do all the things it is useless at as well as the tiny few at which it is actually useful is a step forwards rather than another step back towards the dark ages where nothing at all was understood about software engineering. In addition, the "undocumented" method could easily be made reliable (at least it worked last time I checked on the SQL version I then used) by adding a hint to prevent parallel execution. Of course that certainly could never mean that one can rely on the order of the elements within the string (the definition of SQL SELECT makes it clear that no such order is guaranteed), just that the resulting string, provided it was correctly initialised to a zero length string, contains all the expected elements and nothing else. It would of course be very useful for SQL to provide an operation that handles functional reduction over each of bags (unordered lists), ordered lists, and sets (bags without duplicates), so that there would be no need to involve XML in doing things to which it is irrelevant, but several decades ago the standards group were intransigently refusing to consider anything that the vested interests their members represented didn't already have at an advanced stage of development and as a result we have no such SQL feature and the "undocumented" concatenation method of the question is the nearest that current SQL gets to a small subset of that feature. It is anyway a sure bet that people who see that method for the first time are more likely to understand it without great mental effort than are those who see the FOR XML method for the first time are to understand it.
Tom
January 3, 2015 at 5:24 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply