Sql Server code template & guideline

 

Best Practices for Writing SQL Code: Stored Procedures, Views, and Functions

When working with large development teams or frequent production releases, it's essential to follow a consistent coding structure in SQL Server. This is particularly important in environments with restricted permissions where operations like DROP or CREATE may be limited for security or governance reasons.

The following guide outlines a standardized approach to writing SQL code — especially stored procedures, views, and functions — which enhances maintainability, consistency, and collaboration across teams.

---

📌 Sample SQL Procedure Template

Below is a template you can adapt for writing stored procedures:

/******************************************************************
* DATABASE        : OMAPINTERNET
* PROCEDURE NAME  : usp_SelectAllResults
* CREATED ON      : 04/03/2009
* AUTHOR          : Homer Simpson
* DESCRIPTION     : This procedure returns all results for a given NDC.
* WORK ORDER      : #13798
*******************************************************************
* MODIFICATIONS:
* DATE            : DEVELOPER         : CHANGES
* 08/01/2013      : Himanshu Patel    : Added Indicator column
******************************************************************/

---

✅ Recommended Coding Standards for SQL Procedures

To ensure clarity and best practices in SQL development, follow these guidelines:

• Use CamelCase for naming procedures and variables.
• Implement error handling using TRY...CATCH.
• Avoid cursors whenever possible; use set-based operations instead.
• Indent your code for better readability.
• Add blank lines between logical sections of your code.
• Use ANSI JOINs rather than old-style joins.
• Write clear and meaningful comments.
• Avoid using DROP or CREATE statements inside procedures.
• Declare all variables at the top of the procedure.
• Do not use column numbers in ORDER BY clauses — use column names instead.
• Avoid the use of the GOTO statement.
• Always use primary keys in WHERE clauses for UPDATE or DELETE statements.
• Add SET NOCOUNT ON at the beginning to reduce unnecessary result sets.
• Avoid SELECT *; explicitly define columns.
• Keep transactions short and commit as soon as possible.
• Use BEGIN TRANSACTION and COMMIT blocks for insert/update operations.
• Use sp_executesql instead of EXEC(@sql) for dynamic SQL.
• Avoid using WITH RECOMPILE unless absolutely necessary.
• Never use SQL reserved keywords as object names.
• Structure schemas so that each module has its own schema if the number of tables is large.
• Implement server-side paging to minimize network traffic.

---

🔠 Standard Variable Prefixes

To improve code readability and maintain consistency, use the following naming conventions for variable prefixes based on data type:

No	Data Type	Prefix	Notes
1	tinyint	@ti
2	smallint	@sm
3	int	@in
4	bigint	@bi
5	numeric	@nu
6	hierarchyid	@hi
7	bit	@bl
8	date	@Dt
9	time	@tm
10	datetimeoffset	@do
11	datetime	@Dt
12	datetime2	@Dt
13	money	@mo
14	smallmoney	@Sm
15	decimal	@De
16	float	@fl
17	char	@Ch
18	nchar	@nc
19	varchar	@Vc
20	nvarchar	@Nv
21	uniqueidentifier	@Ui
22	text	@Tx
23	ntext	@Nt
24	sql_variant	@Sv
25	varbinary	@Vb
26	image	@Im
27	xml	@Xm