/*
A couple
of years ago, I wrote the Simple-Talk Prettifier. This is really no more than a stored
procedure that renders SQL code as HTML. I then suggested that the code
should be allowed on Simple-Talk, and had the humiliation of seeing the contemptuous ease with which
Neil and the Red Gate testers found all sorts of examples of code that it failed to render properly. I'd tested it with a
lot of code, but had no idea of the scale of testing that goes on for a product
such as SQL Refactor, SQL Prompt or SQL Doc. Much to my indignation, they used Evil
SQL. Evil SQL? Hmm.. Stuff like this. */
CREATE
TABLE ## ( #
INT )
DECLARE
@ INT set @=9
INSERT
INTO ##
( # )
SELECT @%2
SELECT
*
FROM
##
DROP
TABLE ##
/* can
you imagine that this would execute perfectly happily? Try it! */
/* The
team that writes these tools at Red Gate come across all sorts of oddities. The
other day, they showed me a couple of strange things that inspired me to write
this. */
--first
create a GetDate schema
CREATE
SCHEMA GETDATE
--and a
GetDate table to go in it.
CREATE
TABLE GetDate.GetDate
(
GetDate
DATETIME,
[GetDate GetDate]
DATETIME
)
GO
--and a
function called GetDate
CREATE
FUNCTION GetDate()
RETURNS
TABLE
AS
RETURN
(
SELECT GetDate()
AS [GetDate]
)
GO
-- Now
we can write some startlingly silly code
INSERT
INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate,
[GetDate GetDate])
SELECT GetDate()
AS GetDate,
GetDate FROM
GetDate()
--but we
can do far far siller stuff if we wanted purely because there is no restriction
on what goes between angle-brackets
GO
CREATE
FUNCTION [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
RETURNS
TABLE
AS
RETURN
(
SELECT GetDate()
AS [GetDate]
)
GO
INSERT
INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate,
[GetDate GetDate])
SELECT GetDate()
AS GetDate,
GetDate FROM
[GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
DROP
FUNCTION GETDATE,[GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]
DROP
TABLE GETDATE.GetDate
DROP
SCHEMA GETDATE
/* but
this is boring. We are much better off using a poem by Odgen Nash as the name of
a table. Whoever said that having object-naming policies was a good thing? */
CREATE
FUNCTION
[Candy
Is dandy
But liquor
Is quicker.]()
RETURNS
TABLE
AS
RETURN
(
SELECT GetDate()
AS [GetDate]
)
GO
Select
* from
[Candy
Is dandy
But liquor
Is quicker.]()
/* but
you try leaving out a space! I leave to the reader the exercise of having an
entire database of different tables, all of which use exactly the same poem for
all its object names. This is serious obfuscation. */
/* we
could be a bit more creative and see if we can execute a verse of Macauley's
famous poem 'Horatius'. */
--create
a table with a slightly unusual name
create
table [many a stately market-place;
From many a
fruitful plain;
From many a lonely
hamlet,]
(
[The horsemen and the
footmen
Are pouring in
amain] int,
[, hid by beech
and pine,] varchar(100)
)
--put a
value into this table
insert
into [many a stately market-place;
From many a
fruitful plain;
From many a lonely
hamlet,] ([The horsemen and the footmen
Are pouring in
amain], [, hid by beech and pine,])
Select 1,'an
eagle’s nest, hangs on the crest
Of
purple Apennine;'
/* now,
with that preparation work done, we can execute the third verse */
Select
[The horsemen and
the footmen
Are pouring in
amain]
From [many a stately market-place;
From many a
fruitful plain;
From many a lonely
hamlet,]
Where[, hid by beech and pine,]
Like 'an eagle’s nest,
hangs on the crest
Of
purple Apennine;'
/* If
this seems a bit .er.. florid, you can go minimalist approach to Evil Code */
--first
clear up
DROP
TABLE [many a stately market-place;
From many a
fruitful plain;
From many a lonely
hamlet,]
DROP
FUNCTION [Candy
Is dandy
But liquor
Is quicker.]
--now
let's go neo-bauhaus
Create
table [
]
(# int)
insert
into [
]
select 1
Select
*
from
[
]
create
table [ ] (#
int)
insert
into " " select
1
select
*
from " "
drop
table "
","
"
/*which
twice creates a TABLE, inserts data INTO it, SELECTs the contents and then drops
both tables.
So armed with this little lot
I downloaded the new SQL Prompt 4
and tested it. Did it blink an eyelid? Did it lay it all out nicely? Darn it;
I'd have loved to find code that SQL Prompt would trip over. Memories of their transparent joy when finding problems with the Prettifier still linger. */
