Describing stored procedure parameters

Documents SQL Server 2000, 2005 and 2008 databases.


Moderators: David Atkinson, david connell, Charles Brown

Describing stored procedure parameters

Postby DavidAtABC » Fri May 04, 2007 3:49 pm

Is there a way to attach a description to stored procedure parameters, to all a description of their function, for example - similar to table row descriptions?

And how is this displayed in SQL Doc?

Thanks,

David.
DavidAtABC
 
Posts: 10
Joined: Tue Aug 22, 2006 5:34 pm

Postby david connell » Tue May 08, 2007 12:53 pm

Hi David,
If you are using SQL 2005 then the parameter should be listed just above the SQL Script. (Check out AdventureWorks SPs like [HumandResources].[uspUpdateEmployeeHireInfo] have all their params documented.)However I donot think that parameters are shown/displayed for SQL 2000 databases.
Hope that helps
David
david connell
 
Posts: 167
Joined: Mon Nov 21, 2005 10:12 am

Feature Request

Postby drpsg » Thu Oct 18, 2007 10:35 am

I see that the description of a stored procedure parameter isn't documented.

Here is a example what a free tool can do: http://www.sqlservercentral.com/article ... qldoc/678/

Can RedGate implement this?
drpsg
 
Posts: 6
Joined: Wed Oct 03, 2007 2:50 pm

Postby David Atkinson » Thu Oct 18, 2007 10:59 am

I'm looking at the output for AdventureWorks/Programmability/Stored Procedures/dbo.uspGetBillOfMaterials and SQL Doc has documented the parameters in their own table. As for all the objects, it uses the MS_Description extended property, for example:

Code: Select all
sp_addextendedproperty N'MS_Description', N'Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.', 'SCHEMA', N'dbo', 'PROCEDURE', N'uspGetBillOfMaterials', 'PARAMETER', N'@StartProductID'


Let me know if you're looking for different functionality/behaviour.

Kind regards,

David Atkinson
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby david connell » Thu Oct 18, 2007 12:28 pm

Hi there,
I think that Microsoft had documented all these attributes in books on line.
I have just looked in "SQL Server 2005 Books Online" under the topic sp_addextendedproperty.
[ @level0name = ] { 'level0_object_name' }
Is the name of the level 0 object type specified. level0_object_name is sysname with a default of NULL.

[ @level1type = ] { 'level1_object_type' }
Is the type of level 1 object. level1_object_type is varchar(128), with a default of NULL. Valid inputs are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TYPE, VIEW, XML SCHEMA COLLECTION, and NULL.

[ @level1name = ] { 'level1_object_name' }
Is the name of the level 1 object type specified. level1_object_name is sysname, with a default of NULL.

[ @level2type = ] { 'level2_object_type' }
Is the type of level 2 object. level2_object_type is varchar(128), with a default of NULL. Valid inputs are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER, and NULL.

[ @level2name = ] { 'level2_object_name' }
Is the name of the level 2 object type specified. level2_object_name is sysname, with a default of NULL.

Hope that helps.
Kind regards
David Connell
david connell
 
Posts: 167
Joined: Mon Nov 21, 2005 10:12 am

Postby drpsg » Thu Oct 18, 2007 1:20 pm

okay,

Maybe I have to clarify more:

I have a self made stored procedure and I want to describe the parameters of that procedure. How can I get that description in SQL doc behind the parameters?

Thnx
drpsg
 
Posts: 6
Joined: Wed Oct 03, 2007 2:50 pm


Return to SQL Doc 1

Who is online

Users browsing this forum: No registered users and 0 guests

cron