Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Utility Procedures Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2006 9:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,750, Visits: 1,410
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/utilityprocedures.asp

LinkedIn Profile
Post #254844
Posted Monday, February 27, 2006 6:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811
Verrryyyy nice, David... One of the better articles I've seen on this type of stuff.  And, if someone takes the time to read it, there are all sorts of goodies in the article.  Nice job.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #261609
Posted Tuesday, February 28, 2006 4:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, Visits: 82

Start your utility stored procedures with an SP__, (SP-double underscored). This has three effects:

1. Your utility stored procedures float to the top of the list of stored procedures

2. I've never seen MS use the double-underscore for their procedures, virtually ensuring that your names will be unique from theirs. Additionally, if you want to duplicate the purpose of one of their stored procedures while adding your twist to it, you can do so with a very similar name while maintaining uniqueness. As a simple example, my SP__HELPTEXT doesn't put the column header of TEXT in the output, unlike MS's SP_HELPTEXT.




Post #261904
Posted Tuesday, February 28, 2006 9:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,750, Visits: 1,410
I would steer well clear of depending on sp double-underscore as a determiner for bespoke stored procedures.

Firstly, a double-underscore isn't obviously different from a single underscore.
Secondly, I feel it is a bit ambiguous. If Microsoft suddenly start using double-underscores you would be in trouble. This is why I recommended using you company name in the utility procs.


LinkedIn Profile
Post #262011
Posted Tuesday, February 28, 2006 6:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, Visits: 82

MS is not going to start using SP-double underscores ever. They've stuck with the SP-single underscore for all their versions. Furthermore, the SP-single underscore is thoroughly documented in BOL. They aren't going to change. You can take that to the bank.

And yes, I'm not sure what you mean by "not obviously different". When you use double-underscores, they are obviously different from single-underscores - been doing it for years. They float to the top in browse lists - which again makes them obviously different.

In a color-based SQL editor (Query Analyzer, Rapid SQL, Management Studio), the MS-builtin utility stored procedures turn a different color when you type them correctly - again an obvious difference between SP-single- and SP-double-underscore.

We need to be clear here - utility stored procedures in Master DB are only used on servers that you own. They should never, ever be distributed in an application to a company whose SQL server you do not own.

Thus, putting your company name in the name of the stored procedure is implicitly redundant. All utility stored procedures (that begin with SP-double-underscore) are implicitly from your company.

Finally we also need to be clear that the vast majority of stored procedures that you write will be duplicates of MS stored procedures. Rather they will do super things just the way you want them - take my SP__DATABASE$CREATE stored proc as an example.




Post #262150
Posted Wednesday, March 01, 2006 2:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,750, Visits: 1,410
What I meant was that, depending on the tool and font, double underscore is not visibly that different from single underscore.

LinkedIn Profile
Post #262197
Posted Sunday, March 16, 2008 9:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Does anyone know if this works for SQL2005 and the sys schema?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #470070
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse