How to restrict the stored procedure naming convention in SQL server.

How to restrict the stored procedure naming convention in SQL server.

We can restrict or forced the users to follow stored procedure naming convention by using policy based management in SQL server. I am explaining it by using sql server management studio (SSMS). In this example user must have to keep prefix usp_ in the name of stored procedure in the specific database.

Step 1: Right click on Management -> Policy Management -> Policies and then click on New Policy:

Step 2: In create New policy window:

Name: We can write any name of policy.

In this example we are writing Stored Procedure Naming Rule.

Now we are going to click on new condition in the check condition drop down to add the conditions.

Step 3: In the create new condition window:

Name: We can write any name of the condition. In this example we are keeping Procedure Name Condition.

Facets: Choose stored procedure in the drop down.

Expression:

Field: On which property we want to put condition. In this example we are choosing @Name which implies name of the stored procedure.

Operator: In this example we are choosing LIKE operator.

Value: 'usp_%'

Now click on the OK button.

Step 4: In this previous window that is create new policy window:

Enabled: We are checking it to enable it after its creation.

Evaluation Mode: In this example we are choosing On Change:Prevent.

It means this event will occur if user will try to create or alter stored procedure name.

Step 5: This naming conditions is applicable in the all the database. If we want we can put this restriction in the specific database. For this click on the Every drop down for database and a new conditions. It will open new condition window. Write followings:

Field: @Name

Operator: IN

Value: Name of databases. In this example: Array(my_Database','Employee')