{"id":110,"date":"2007-01-15T00:00:00","date_gmt":"2006-05-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-ultimate-excuse-database\/"},"modified":"2021-05-11T15:57:46","modified_gmt":"2021-05-11T15:57:46","slug":"the-ultimate-excuse-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/the-ultimate-excuse-database\/","title":{"rendered":"The Ultimate Excuse Database"},"content":{"rendered":"

—Editor’s Note<\/em>—
\nThis article, describing Phil’s “ultimate excuse generator”, was first published in December 2005. The plug-and-play version of the tool was recently removed from the Red-Gate.com website, but I couldn’t stand by and let it disappear for good, so it’s now hosted on Simple-Talk. This seemed like a good enough reason to give the article a polish and re-publish it. If you missed it the first time, you’re in for a treat!
\n—End Editor’s Note<\/em>—<\/p>\n

Failed Project? Need an excuse fast?<\/h2>\n

Recently, I received a desperate plea for help from a Simple-Talk reader, who cited the following dilemma:<\/p>\n

Phil,<\/b><\/p>\n

The project I’m working on is about to go belly-up. Can you come up with some plausible reasons for the failure that would not reflect poorly on my work?<\/b><\/p>\n

Thanks in advance, Dave<\/b><\/p>\n

This, of course, is not one of the classic SQL problems addressed in textbooks. It is quite difficult to give a solution that conforms to the SQL-92 standard.<\/p>\n

There are many ways of approaching the problem, and I’ll go over one solution – my challenge to other readers to se if they can come up with something better! If you want to test drive the excuse generator now, simply click on the link below:<\/p>\n

[Sadly the excuse generator finally broke down. We have no excuse for this.]<\/p>\n

The versatility of T-SQL functions<\/h2>\n

Whatever its shortcomings my solution serves as a good way of highlighting the versatility of T-SQL functions<\/b>. You just need to create a SQL Server database and into it load a few simple functions. These\u00a0functions will provide facile and convincing arguments as to why a project has failed, thereby creating a smokescreen that will safely conceal the real culprits.<\/p>\n

A T-SQL function for picking random data<\/h3>\n

We start with a function that will pick a random phrase or excuse from a list. This is a modification of a technique I use\u00a0to populate a database with plausible data for checking indexing strategies, and for testing a fully populated database under load.<\/p>\n

The first thing to remember about functions is that the use of the rand()<\/b> operator is forbidden. This is for a very good reason: its use renders the function non-deterministic. We cheat by disguising it as a view.<\/p>\n

NOTE<\/b>:
\nThis is not guaranteed to work in subsequent releases of SQL Server – though it works just fine on SQL 2000 and 2005.<\/i><\/p>\n

CREATE\u00a0VIEW\u00a0vRandomNumber \r\nAS \r\nSELECT\u00a0RAND()\u00a0AS\u00a0RandomNumber \r\nGO<\/pre>\n
Now we provide the key function that picks a phrase from a list. This, by itself, is sufficient for simple tasks such as giving a weather forecast, doing a psychic reading, or selecting an appropriate IT architecture:<\/p>\n
CREATE FUNCTION ufsOneOf \r\n\/* \r\nDescription: \r\nPicks one of a delimited list. Here we have a version which has '|'\r\nhard-wired as the list delimiter.\r\ntest: \r\nselect dbo.ufsOneOf('') \r\nselect dbo.ufsOneOf('|||') \r\nselect dbo.ufsOneOf(null) \r\nselect dbo.ufsOneOf('one|two|three') \r\n*\/ ( @String VARCHAR(8000)--input string of a list of alternatives \r\n     )\r\nRETURNS VARCHAR(8000)--the list item selected \r\nAS\r\n    BEGIN  \r\n        DECLARE @ii INT; \r\n        DECLARE @Substring VARCHAR(255); \r\n        DECLARE @which INT; \r\n        DECLARE @Delimiter CHAR(1); \r\n\r\n        SELECT  @Delimiter = '|'; \r\n\r\n--select a random integer between 1 and the number of list items \r\n        SELECT  @which = ( SELECT   RandomNumber\r\n                           FROM     vRandomNumber\r\n                         ) * ( LEN(@String) - LEN(REPLACE(@String, @Delimiter,\r\n                                                          '')) + 1 ) + 1; \r\n\r\n        SELECT  @ii = 1 ,\r\n                @Substring = ''; \r\n--And go to the item you want by iteration.\r\n--This will please the procedural boys \r\n        WHILE @ii <= @which\r\n            BEGIN\r\n  --if the impossible has happened or he has passed a null string\r\n                IF ( @String IS NULL\r\n                     OR @Delimiter IS NULL\r\n                   )\r\n                    BEGIN \r\n                        SELECT  @Substring = ''; \r\n                        BREAK;  \r\n                    END; \r\n                IF CHARINDEX(@Delimiter, @String) = 0\r\n                    BEGIN  \r\n                        SELECT  @Substring = @String; \r\n                        SELECT  @String = ''; \r\n                    END;  \r\n                ELSE\r\n                    BEGIN \r\n                        SELECT  @Substring = SUBSTRING(@String, 1,\r\n                                                       CHARINDEX(@Delimiter,\r\n                                                              @String) - 1); \r\n                        SELECT  @String = SUBSTRING(@String,\r\n                                                    CHARINDEX(@Delimiter,\r\n                                                              @String) + 1,\r\n                                                    LEN(@String)); \r\n                    END; \r\n                SELECT  @ii = @ii + 1; \r\n            END;  \r\n\r\n        RETURN (@Substring);  \r\n    END;  \r\n\r\nGO  <\/pre>\n
Examples of use include this weather forecaster which seems to be at least as accurate as the Met Office or the National Weather Service:<\/p>\n
Select dbo.ufsOneOf('Rain|Mist over the hills, Clearing later|\r\nDry in the east, Rain spreading from the west\r\nlater|Rain heavy at times, becoming clearer\r\nlater|Generally dry|Showers, more organized\r\nrain spreading from the west|Scattered showers|\r\nRain spreading from the east|Dry interludes|\r\nBecoming overcast later')<\/pre>\n
Picking your excuse<\/h3>\n
Now we build this function into a collection of word banks and phrase banks that we can then use to provide the basis of an IT strategy document; in this case, plausible reasons for the failure of a project.<\/p>\n
NOTE:
\nIn order to control the length of the listing, some of the inputs, contraints etc were omitted. The full source code can be obtained from the code download bundle.<\/em><\/p>\n
CREATE FUNCTION dbo.ufsSelectRandomPhrase \r\n\/* \r\nDescription \r\nSo here we have a function that returns, at random,\r\none of a series of words or phrases.\r\nCurrently these are set to:\r\n\r\nA--conceptual processes-- \r\nB--excuses-- \r\nC--inputs--  \r\nD--constraints-- \r\nE--types of projects-- \r\nF--pejorative words \r\nG--things that must be good \r\nH--misconceptions-- \r\nI--unpleasant results \r\nJ--unpleasant things \r\nK--negatives \r\nL--neutral modifiers \r\nM--positive modifiers \r\nN - S --the six main parts of a sentence \r\nT-- process \r\n\r\nNote that the function can pass back a string containing\r\na placeholder of the format %x where x is one of the\r\ncharacters A-T \r\n\r\n\r\nTest \r\n       Select dbo.ufsSelectRandomPhrase('A') \r\n       Select dbo.ufsSelectRandomPhrase('I') \r\n\r\n*\/ ( @type CHAR(1) )\r\nRETURNS VARCHAR(8000)\r\nAS\r\n    BEGIN  \r\n\r\n        RETURN( \r\nCASE @type --processes-- \r\nWHEN 'A' THEN  \r\ndbo.ufsOneOf('process|organization|technology|planning\r\n|methodology|documenting|design\r\n|implementation|contingency planning\r\n|change control systems') \r\n--excuses-- \r\nWHEN 'B' THEN  \r\ndbo.ufsOneOf('unclear objectives|changing objectives\r\n|insufficient resources\r\n|impossible schedules\r\n|unrealistic expectations\r\n|unclear roles and responsibilities\r\n|corporate  politics|poor communication\r\n|personnel turnover|changing technology\r\n|constraining rules and regulations\r\n|lack of sponsorship|poor planning\r\n|unclear goals and objectives') \r\n--inputs--  \r\nWHEN 'C' THEN \r\ndbo.ufsOneOf('management support\r\n|senior management buy-in\r\n|resource allocation\r\n|project control|pre-planning') \r\n--constraints-- \r\nWHEN 'D' THEN \r\ndbo.ufsOneOf('scope|resources|schedule\r\n|targets|requirements') \r\n--types of projects-- \r\nWHEN 'E' THEN \r\ndbo.ufsOneOf('software packaging and distribution\r\n|CMS|accounting|managing workflow') \r\n--pejorative words \r\nWHEN 'F' THEN \r\ndbo.ufsOneOf('blindly|foolishly|shortsightedly\r\n|unthinkingly|incompetently') \r\n--things that must be good \r\nWHEN 'G' THEN \r\ndbo.ufsOneOf('core services\r\n|comprehensive solutions\r\n|industry best-practice') \r\n--misconceptions-- \r\nWHEN 'H' THEN \r\ndbo.ufsOneOf('the over-marketing of product features\r\nby software vendors\r\n|the belief that a particular technology\r\nis a \"silver bullet\"\r\n|the reluctance to invest heavily in\r\nan area that is a \"cost center\"\r\n|the overambitious goals\r\n|a lack of understanding of IT processes\r\n|the belief that distributed computing is\r\ntechnology, not process driven\r\n|the belief that technological change\r\ncan be rapidly absorbed by an organization') \r\n--unpleasant results \r\nWHEN 'I' THEN \r\ndbo.ufsOneOf('An inability to properly support or manage\r\nthe technology, resulting in increasing costs\r\n|Inefficiencies in the service provided to\r\ncustomers\r\n|An inability to meet service-level agreements\r\n|Disconnect among the staff due to sudden\r\nintroduction of change to their environment,\r\nresulting in %J and\/or %j')  \r\n--unpleasant things \r\nWHEN 'J' THEN \r\ndbo.ufsOneOf('complexity|resistance|attrition|confusion\r\n|apathy|anxiety') \r\n--negatives \r\nWHEN 'K' THEN \r\ndbo.ufsOneOf('superfluous|inappropriate|flawed\r\n|insufficient|harmful|vague|unhelpful\r\n|inefficient|poor|inadequate|unfocused\r\n|under-resourced') \r\n--neutral modifiers \r\nWHEN 'L' THEN \r\ndbo.ufsOneOf('main|major|subtle|key|contributing\r\n|influential|high level|ameliorating\r\n|inherent') \r\n--positive modifier \r\nWHEN 'M' THEN \r\ndbo.ufsOneOf('sufficient|adequate|focused|beneficial') \r\n--sentence components \r\nWHEN 'N' THEN  \r\ndbo.ufsOneOf('|||As with many projects,\r\n|Typically of projects of this scale,\r\n|We are now realizing that|\r\n|Surely|To be absolutely frank,\r\n|Preliminary examinations reveal that\r\n|An in-depth analysis suggests that\r\n|I think it is fair to say that\r\n|It is generally thought that|\r\n|We must conclude that, finally') \r\nWHEN 'O' THEN  \r\ndbo.ufsOneOf('one of the %L causes\r\n|a %L factor which was responsible\r\n|the blame|one of the %L reasons\r\n|a %L factor\r\n\r\n\r\n|the %L difficulties')\r\nWHEN 'P' THEN  \r\ndbo.ufsOneOf('for the|for any of the\r\n|for all the issues around the\r\n|for what is usually termed the|\r\n|for what is probably the\r\n|of the avoidance of') \r\nWHEN 'Q' THEN  \r\ndbo.ufsOneOf('lack of %M Change Control Systems\r\n|well-defined architectures\r\n|overrun of initial cost estimations\r\n|%K resolve to follow the plans\r\n|inadequate planning|project slip\r\n|budget overrun|%K productivity\r\n|scope-creep\r\n|lack of support from senior management') \r\nWHEN 'R' THEN  \r\ndbo.ufsOneOf('was because of|was due to|rests with\r\n|cannot be attributed to\r\n|may be linked with\r\n|is undoubtedly due to\r\n|should be seen in the context of') \r\nWHEN 'S' THEN  \r\ndbo.ufsOneOf('changing requirements\r\n|personality conflicts\r\n|%K upper management\r\n|restricted budget|restricted time\r\n|power struggles\r\n|%K elicitation and validation\r\nof requirements\r\n|commitment|overambitious goals\r\n|incompetent staff\r\n|a culture dependent on maintaining\r\n\r\nthe status quo\r\n|a %K transition strategy') \r\n--processes \r\nWHEN 'T' THEN \r\ndbo.ufsOneOf('business|payroll|accounting|group\r\n|management|sales|marketing')             \r\nELSE \r\n   'error -bad placeholder' \r\nEND \r\n       );  \r\n    END;  \r\nGO  <\/pre>\n
Random excuses become plausible waffle<\/h3>\n
Now all we need to do is have a function that substitutes phrases in the right place. Normally, this would have a mundane use such as coming up with customer addresses for dummy data, but here we give it a more dignified purpose:<\/p>\n
CREATE FUNCTION dbo.ufsWaffle\r\n    (\r\n      @formatString VARCHAR(8000)\r\n    )  \r\n\/* \r\nDescription:\r\nSearches for the next placeholder repeatedly in a string,\r\nand substitutes a random phrase until there are no more.\r\n\r\nRequires:\r\ndbo.ufsSelectRandomPhrase\r\n\r\nExample:\r\nselect dbo.ufsWaffle('%N %O %P %Q %R %S') \r\n\r\n*\/\r\nRETURNS VARCHAR(8000)\r\nAS\r\n    BEGIN  \r\n        DECLARE @Where INT; \r\n\r\n        WHILE 1 = 1\r\n            BEGIN  \r\n                SELECT  @Where = CHARINDEX('%', ISNULL(@formatString, '')); \r\n--If we are out of % placeholders return the @formatString  \r\n                IF @Where = 0\r\n                    BREAK;  \r\n--If the delimiter is not in the Args list then do one last\r\n--replacement  \r\n                SELECT  @formatString = STUFF(@formatString, @Where, 2,\r\n                                              dbo.ufsSelectRandomPhrase(SUBSTRING(@formatString,\r\n                                                              @Where + 1, 1))); \r\n            END;  \r\n        RETURN (@formatString);  \r\n    END;  \r\nGO  <\/pre>\n
So, invoking:<\/p>\n
SELECT\u00a0dbo.ufsWaffle('%N\u00a0%O\u00a0%P\u00a0%Q\u00a0%R\u00a0%S')\u00a0<\/pre>\n
…repeatedly will supply as many plausible reasons as you wish.<\/p>\n
A versatile bullshit generator<\/h3>\n
However, you will notice a certain repetitive sameness about the previous solution. A more subtle approach is to randomize the sentence structure.<\/p>\n
CREATE  PROCEDURE spCreateEssay \r\n\/* \r\nDescription \r\n This will produce a block of text based on picking the built-in\r\n phrases at random and stringing them together. \r\n Then the placeholders are substituted. \r\n        \r\nExample \r\n Execute spCreateEssay 'Red Face Software Ltd'\r\n\r\nExample output \r\n Select dbo.ufsWaffle('%N %K %A %R %S') \r\n*\/ \r\nAS \r\nSET nocount ON \r\nCREATE TABLE #sentences (sentence VARCHAR(500),\r\ntheorder numeric(10,9)) \r\n\r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT 'Without %M %A, a project will eventually fail!\r\nOne of the reasons why IT projects suffer from\r\nthis to a greater degree than other industry\r\nprojects lies in %B.',RAND() \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT 'A %L consequence of  %H is either %I or %I.',RAND() \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT 'An analogy of the link between %A, %A and %A is\r\nthe project management constraint triad --%D, %D and %D.\r\nChanges to any one of these constraints affect\r\nthe others.',RAND()  \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT 'If any organization %F implements a new tool for %T\r\nwithout considering the underlying %T processes or the\r\norganizational structure required to operate and support\r\nthe technology,then failures can and more than likely\r\n\r\nwill occur: ',RAND() \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT 'Business environments these days are characterized by %B,\r\n\r\nand acceleration of everything from %J to %J. \r\nIT has been one of the major drivers of this.',RAND() \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT '%N %O %P %Q %R %S',RAND() \r\nINSERT INTO #Sentences (sentence,theOrder)\r\nSELECT '%N %K %A %R %S',RAND() \r\n\r\nDECLARE @outputString VARCHAR(8000) \r\n\r\nSELECT @OutputString=COALESCE(@OutPutString,'')\r\n\t+dbo.ufsOneOf(' | | | ')\r\n\t+ dbo.ufsWaffle(sentence\r\nFROM #sentences ORDER BY theorder \r\n\r\nSELECT @OutputString \r\nGO  <\/pre>\n
So, at the click of a mouse we generate random, dignified excuses that spread the blame evenly and safely, and away from any particular individual.<\/p>\n
Start generating excuses…<\/h3>\n
As with all code or prose, some editing and cleanup of the output is required, but it is easy to see how versatile and useful this tool can be.<\/p>\n
The full source code is available from the CODE DOWNLOAD<\/strong> link (in the box below the star ratings at the top of this page).<\/p>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
Is your IT project in trouble? Do you need to create a smokescreen so that no-one can pin the blame on you? No problem! Marvel at Phil Factor’s versatile use of T-SQL functions in creating the “ultimate excuse database” and then start generating your excuses immediately! <\/p>\n
…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143532],"tags":[4168,4338,4339,4336,4150,4151,4213,4183,4337],"coauthors":[6813],"class_list":["post-110","post","type-post","status-publish","format-standard","hentry","category-tools-sql-server","tag-database","tag-excuse-generator","tag-generating-test-data","tag-smo","tag-sql","tag-sql-server","tag-sql-tools","tag-t-sql","tag-t-sql-functions"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=110"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110\/revisions"}],"predecessor-version":[{"id":72971,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110\/revisions\/72971"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=110"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}