Post
Ask Question

Good Practices to Write Stored Procedures in SQL Server

Introduction

In this article, we will learn about Good Practices for Write Stored Procedures in SQL Servers.

Follow the below steps to write a clean store Procedure:

  1. Use proper indentation for the statements in SQL Server. It will improve readability.
  2. Write the proper comments between the logic. So the others can understand quickly.
  3. Write all the SQL Server keywords in the CAPS letter. For example, SELECT, FROM, and CREATE.
  4. Write the stored procedure name with fully qualified names. 
    CREATE PROCEDURE [dbo].EmployeeSalaryCalculation
  5. Always try to declare the DECLARATION and initialization at the beginning of the stored procedure.
  6. It is not recommended to use more variables in the procedure. It will occupy more space in the memory.
  7. Do not write the stored procedure name beginning with sp_. It is reserved for the system-stored procedures in SQL Server, and when the request comes to the SQL Server engine, it will be considered to be a system-stored procedure and look for it in the master database. After it understands that this is a user-defined stored procedure, it requires a bit more response time. So name the procedure name with another prefix such as proc_.
  8. Set the SET NOCOUNT ON option at the beginning of the stored procedure to avoid unnecessary messages like the number of rows affected by the SQL Server.
  9. Try to avoid the temp table in the stored procedure. Stored procedures usually use a cached execution plan to increase performance. When you use the temp table, it will do the compilation every time.
  10. Do not use the select all columns (SELECT *) option; use only specific columns to query the result.
  11. Try to avoid the cursor in the stored procedure. It will consume more memories. It will degrade the performance of the stored procedure. Try to use the table variable and WHILE loop statement to iterate the query result set.
  12. Set the default value to the parameter and always set the size of the variable to be equivalent to or more than the table field column length. For example, Name (10) in the table, but if you give Name(25) in the procedure, then you will get the run time error time "string truncated error".
  13. Use the Try catch statement properly in the stored procedure to handle the errors in the runtime.
  14. Move the complex query into views.
  15. If you want to return the single column result, then prefer to use the output statement to return the result to the calling program rather than the table result.
  16. Avoid the sub-queries and use the INNER JOIN. Try to avoid the filtering condition in the where clause, and it can be written in the joining time itself. When joins the table itself, it will be filtered, and it will filter again from the joined result table.
  17. Use the SELECT TOP 1 in the existing condition checking.
  18. Do not do this 
    SELECT @name=name FROM employees WHERE name like '%rob%'
  19. This will give the run time error when returning more than one result. 
    SELECT TOP 1 @name=name FROM employees WHERE name like '%rob%'
  20. It is always recommended to use the TOP 1 in that case. The result may differ from what is expected.
  21. Avoid the nested IF statements and use the CASE statement. It will execute the matching part immediately.
  22. Dynamic Queries - Try to minimize the usage of dynamic queries. If you are using a dynamic query like
    SELECT * FROM mydb.dbo.emp where empid = @eid, then there is no problem.
  23. You can supply a value for the @eid parameter, and there is no recompilation of the execution plan in the database cache. But if you are using a SQL query like SELECT * FROM emp where empid = " + @eid and supply a parameter.
  24. Use the ORDER BY and DISTINCT, TOP only when required. The SQL Server engine will get the result first, and it will do again the query execution for these operations.
  25. It is recommended to use a Table variable when the result set is small. It is always in the memory, and when the limit exceeds, it will be created as a table in the temp. But the temp table will be created on the temp database and that makes it slower.
  26. Use the proper indexing of the columns in the table. Do not create an index on the columns that are not used anywhere in the where clause. It will require an extra roundtrip to query the result.

Advantages of the stored procedure

  • It will improve the execution. It will compile once and be stored as an object under the database. The same execution plan will be used for all subsequent executions.
  • It will reduce network traffic. It will have only a few bytes of parameters. But the inline query will make more traffic.
  • It will be stored in the SQL Server database, and any number of applications that return in multiple languages/applications can utilize the same stored procedure.
  • The permission can be set to the stored procedure.
  • The stored procedure can be changed the logic without redeploying the application in certain circumstances.
  • The application developers can work in the front end, and the SQL developers can write the SQL Server. 

Conclusion

In this article, we learned about Good Practices for Write Stored Procedures in SQL Servers.


Similar Articles