Yesterday I was reviewing one of my latest projects and I found that one of the developers had created two stored procedure with “SP_” prefix. Generally “SP_” prefix is used for System stored procedure name; look at one of my past article how to create your own system SP, if You will create your own stored procedure with “SP_”, It will not give you any error, it will get executed successfully but it is really a overhead on processing as whenever compiler will get prefix “SP_” it will going to look into system SPs list, once it won’t get it from there, will look at user SP list. Apart from this, there should be some naming convention rules for user defined object that is what I believe so I thought to put some policy enforcement so that, even by mistake, nobody can do it.
Since this is not a tutorial article for Policy Management, I will not going into deep dive in Facets, Condition and policy, rather I will use it to show you one practical example.
1.) Create your condition by right clicking on “Condition” tab under Instance Name-> Management-> Policy Management-> Conditions-> New Conditions
2.) In a conditions dialog box, give Name as a “SPNamingConventions”, select facet “Multipart Name” and in expression give @Name NOT LIKE ‘SP_%’
3.) After clicking on “OK” button in above dialog box, right click on “Plolicies”, select “New Policy” and fill out details as mentioned in below screen capture.
Finally you are giving Policy Name “Stored Procedure Naming Convention Policy”, making it enable, which is MUST, by check box given. Select the condition you created in step # 2 and also selecting the target from given list. Once you done with that, don’t forget to select “On Change: Prevent” Evaluation Mode and also don’t forget to click on “OK” button
Once you are done with that, try to create stored procedure with prefix “SP_”. I am doing it in my server in AdventureWorks database; however you can try it in your own database.
Use AdventureWorks
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
3 comments:
How can we do this same thing SQL Server 2005.
It is not possible to do it in SQL Server 2005. This facility is introduced from SQL Server 2008 only
Thank you for this article. Very helpful.
My question is this. How would I use something like this to enforce the use of a stored proc to have a dbo. as a prefix?
I've tried using @Schema with various values to no avail.
Suggestions?
Post a Comment