The Level 6 Architect needed to pass some XML into a stored procedure that would be the payload for a Service Broker message.
"Piece of cake," I said, "Here you go!" and passed him the following procedure...edited for brevity and to protect the innocent.
"Oh no no no no no. That will never do. We have a strict requirement that we cannot pass XML parameters into a procedure, we must pass in a varchar because the XML schema will change dynamically with the...". My eyes and ears started to glaze over as I heard a litany of ridiculous reasons why an XML datatype could not be used.
"Let me get this straight...does our JDBC driver support SQL Server's XML datatype?"
"Then we should be passing in the XML data to a strongly typed parameter, a la one declared as XML."
"No! We cannot do that. Use a varchar(500) instead. Just do as you are told."
"No problem-o Mr. Level 6. Here you go. But I suggest you sanitize your data in the Java tier to be sure you will never overflow a varchar(500). Are you sure that is big enough? XML tends to be obese." I was trying not to lose my cool.
"Just do as you are told."
"OK, just remember that when this blows up in your face...I told you so."
Fast forward 12 months when this code is in production for mission critical, life-or-death data situations...
Level6: "Your gd stored proc is throwing errors everywhere."
Me: "Can you show me the error please?"
Msg 9400, Level 16, State 1, Procedure FooBar, Line 8
XML parsing: line 1, character 500, unexpected end of input
Me: "That's interesting that it fails exactly at character 500 every time, don't you think? Do you remember when I told you this would blow up in your face if you didn't sanitize your inputs?"
Level6: "Yeah, so what?"
Me: "So, you ignored me and you are passing in more than 500 characters and the XML parser in SQL Server cannot parse the string as well-formed XML. I told you to sanitize your parameters before calling the procedure. You chose to ignore me. This is your problem."
Level6: "Prove it. SQL Server has to report an error when we overflow the datatype."
Me: "Here you go."
Level6: "There is no way we are possibly passing in more than 500 characters of data. Impossible. 500 characters should be at least four times more than we could possibly ever send."
"Well, apparently you are wrong. I bought my pants two sizes too big last year and now my butt doesn't fit in 'em. I figured two sizes bigger should be more than enough. It wasn't. The difference is, my pants don't silently truncate
the extra girth like SQL Server does. My pant seams scream loudly, "hold together men!" Maybe what you need to do is make sure your Java code doesn't overflow the datatype just like I need to make sure my butt doesn't overflow the pants."
Level6: "Well, SQL Server should not silently truncate input data. That's just crazy."
Me: "It's only been doing that since at least 1990. Go fix your code. And remember, a good architect uses the most specific datatype possible and always sanitizes the input."