January 31, 2006 at 9:47 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/utilityprocedures.asp
February 27, 2006 at 6:52 am
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
Change is inevitable... Change for the better is not.
February 28, 2006 at 4:49 am
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.
February 28, 2006 at 9:37 am
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.
February 28, 2006 at 6:37 pm
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.
March 1, 2006 at 2:22 am
What I meant was that, depending on the tool and font, double underscore is not visibly that different from single underscore.
March 16, 2008 at 9:46 pm
Does anyone know if this works for SQL2005 and the [font="System"]sys[/font] schema?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply