One thing SQL Server seems to be lacking is the ability to categorise stored procedures (SPROCS), or even just organise them into separate “folders” within the database. This might not be much of an issue when you only have a few procedures, but can become a real issue for developers using the SQL Server Management Studio software to develop.
How I do it
Whilst developing my final year project at University, I decided to address this issue. I wanted a method of being able to quickly scroll through potentially hundreds of SPROCS, in order to find the specific procedure that I was looking for. Not only this, but I wanted to group procedures together into the type of queries that they ultimately run eg SELECT, INSERT etc…
In this example I have chosen to demonstrate how naming SPROCS for ASP.NET Routing could be done.
Firstly we start with the functionality that the SPROC is for. In this case “ROUTING”. This could also have been other things such as “ORDERS”, or “COMMENTS”. Since SQL Management Studio orders everything alphanumerically, we have managed to ensure that all SPROCS relating to “ROUTING” are now grouped together.
Similarly with the type of query we want to run. All query types will be grouped together.. GET.. INSERT etc. This makes finding what you are looking for so much quicker and easier. Not only this, but the names of your queries are also now “Self Documenting”, and precitable.
Finally, finish the name with some useful information. In the example, I also showed how I was going to run the query. “ROUTING_DELETE_RouteByID”. Just by looking at the name, you can take a pretty well educated guess as to what is needed in terms of parameters for the query. In this case, we’re deleteing a specific record by it’s “ID” and so some kind of “ID” will need to be passed as a parameter.
It’s also worth noting that I opted to use capital letters for the first and second parts of the name. In my opinion this just makes the names stand out that much more in the SPROC list.
How do you name your stored procedures?
I hope you found this useful, and helpful.