I'm using a 'MS_Description' extended property to document parameters in a stored procedure. This parameter's documentation requires more structure than just plain text. That is I would like to describe: "if value = x, then action A is taken; if value = y, then action B is taken, …".
What is the best way to store this structure?
The following approaches occured to me:
-
Use html within the 'MS_Description', e.g.
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N' Selects the foobar. Can have the following values: <dl> <dt>x</dt><dd>does A</dd> <dt>y</dt><dd>does B</dd></dl>' @level0type = N'SCHEMA', @level0name = [dbo], @level1type = N'PROCEDURE', @level1name = N'SE_DemoProc' @level2type = N'PARAMETER', @level2name = N'DemoPar';
- Would developpers DBAs automatically parse html in their heads?
-
Use visual studio xml document tags the extendedproperty,
@value
would become:@value = N' Selects the foobar. Can have the following values: <list type="bullet"> <item><term>x</term><description>does A</description></item> <item><term>y</term><description>does B</description></item> </list>'
-
Does SSMS, visual studio, and/or other tools interpret Xml document tags in MS_Description extended properties?
-
Does extra semantics of '', '', and '' improve people's understanding more than the html?
-
-
Use a different extended property to document the values that a parameter excepts?
- Which property is commonly used for this?
-
How to encode these multiple values?
As far as I know, an extended property can only occur once for a given (schema, procedure, parameter)-combination.
-
Using newlines, tabs, and '*' to format the plain text will not work since whitespace is collapsed from sql scripts.
- does escaping newlines and tabs work?
-
Perhaps, I should avoid the mode parameter altogether and split this stored procedure into several distinct precedures. (But that would be a different question and out of scope for this one.)
Best Answer
Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway).
A block comment in a view, function, trigger or stored procedure is definitely stored in the database. Everything between the two
GO
batch separators is stored as the body (though I confess I don't know which visual editors might strip them, since I just use a standard query editor):(I will also confess that comments stored before
CREATE PROCEDURE
or after the finalEND
may be prone to inadvertent exclusion when someone modifies the procedure, so it may be safer to keep the comments inside the body.)A little trickier to store documentation for tables and columns inside the database without using extended properties. But again, I'd argue these should be documented externally anyway. If you're going to bother writing documentation for these things, you may as well put it somewhere that will still exist if the database goes south.