SQL Server Database Naming Conventions Best Practices

Coding conventions and Coding standards define a programming style. Coding standards keep the code consistent and easy for the entire team to read and refactor. It is simply a set of rules and guidelines for the formatting of source code. 

Benefits of Coding Standards

• Maintainable Code and Easy to Understand
• Code Integration
• Uniform Problem Solving
• Minimize Communication (It saves time and cost)

So in this article I will explain about what are the good programming practices for SQL naming conventions. Find the below PL SQL naming conventions :- 

microsoft sql server best practices

Table of Contents

• Overview
• SQL Query & T-SQL Coding Guidelines
• Proper Use of Joins
• Do Not Use Functions in Joins and Where Clauses
• Avoid EXISTS and LEFT/OUTER JOIN
• Whenever Possible, Use “=” instead of “< >” Operators
• Data Types in database object definitions (parameters) should match Data Types in tables
• Be Highly Selective When Writing a WHERE clause, OR statement or CASE statement
• Select Only Rows / Columns That Are Needed
• No Hard-Coded Parameters or Variables for Static Values
• Every Query Must Have a Corresponding Index
• Do Not Use 3- or 4-Part Database Object Names
• Perform Selective Updates
• Keep Your SQL as Basic As Possible
• Use Functions on Variables, Not on Columns
• No Query / Index Hints
• Do Not Add DISTINCT By Default
• Maintain good formatting in SQL code
• No nesting of functions, views, and stored procedures deeper than 1 level
• If the query contains more than 10 OR conditions for a single column, populate a temp table variable with search parameters and join on it
• No case statements on strings longer than 10 characters
• Avoid Indexed Views
• Avoid Using Cursors
• No Triggers
• User-Defined Functions
• Avoid Nested Functions
• Temporary table and Table Valued Function (TVF) practices
• Use Common Table Expressions instead of temp tables
• Remove all obsolete/abandoned objects
• Avoid linked server / cross-database SQL calls
• Circular References
• Avoid the use of OPENQUERY
• About LIKE Operator
• Avoid dynamic SQL
• Using the ORDER BY Clause
• Using the MERGE Statement
• Stored Procedure Formatting
• Creation Standards
• Use of NO LOCK Discouraged
• Comment Block
• Code Formatting
• SQL Code Naming Conventions & Standards
• Error Handling
• Sample Code

SQL Query & T-SQL Coding Guidelines

These guidelines provide general practices for writing efficient SQL queries and should be followed when writing any form of SQL, whether in-line, in stored procedures, views, etc.  This also applies to T-SQL code – these are guidelines for code that is more complex than standard SQL. 

Proper Use of Joins

• Use table aliases when joining multiple tables. In a join query prefix each column name with its table alias, even when the column name is unique across all joined tables and column alias also remove the column ambiguous problem when we join on multiple tables with same column names.

• Avoid using UNION; use direct INNER JOIN statements instead.

• Avoid use of UNION ALL or LEFT OUTER JOIN wherever possible as this reduces processing time to retrieve the result set.

• Do not use the “old” join syntax (where table names are comma-separated in the FROM clause), including outer-join syntax “*=” and “=*”.

• Always try to join numeric fields rather than nvarchar/varchar values.

• Avoid use of cross join (tesian product) if possible

Do Not Use Functions in Joins and Where Clauses

• SQL Server is designed to quickly process data in sets, rather than at individual record’s level.  Using functions in joins and where clauses forces SQL to evaluate the data one record at a time, instead of as a set, which results in poorly performing queries.

Avoid EXISTS and LEFT/OUTER JOIN

• Don’t use sub-queries and EXISTS without talking and verifying with a members of the team.  Avoid OUTER / LEFT joins unless absolutely necessary, as this can cause the query to retrieve extraneous data. 

Whenever Possible, Use “=” instead of “< >” Operators

• SQL Server performs equals (= or !=) operations more efficiently than the comparison operators (< >).  When possible, use the equals (=) operator. 

Data Types in database object definitions (parameters) should match Data Types in tables

• Mismatched data types force SQL Server to apply implicit data conversions, which lead to poor execution plan generation and unnecessary over-utilization of hardware resources.

Be Highly Selective When Writing a WHERE clause, OR statement or CASE statement

• Reducing the dataset being traversed by SQL Server as early in the WHERE / OR / CASE clause as possible is key to better performance.  Put the most highly selective condition first in the SQL code (beginning of the WHERE clause).  Once this is narrowed down at the beginning of the clause, the subsequent filters will be faster.

• When writing a SQL statement with multiple predicates, determine the predicate that will filter out the most data from the result set and place that predicate at the start of the list. By sequencing your predicates in this manner, the subsequent predicates will have less data to filter.

Select Only Rows / Columns That Are Needed

• Each qualifying row & column has to make it through the long journey from storage, through the buffer pool, possible sort and translations, and then is delivered in the result set. The database processor should do all data filtering; it is inefficient to retrieve a row, test that row in the code and then filter out that row if that row can be filtered out in the SQL statement.

No Hard-Coded Parameters or Variables for Static Values

• Hard-coding parameters into SQL code makes it difficult to support and maintain.  Instead, lookup tables should be used to maintain the lists of identifiers and their descriptions.

• Do not use variables for constant / static values in the SQL statement – use the actual value

Every Query Must Have a Corresponding Index

• Indexes are critical for performance of SQL queries.  In order to ensure good performance, execution plans of every new query must be analyzed and, if necessary, new indexes must be added.  Evaluation of execution plans needs to be performed on databases that are similar in size to production. If possible, try to create indexes on column that have integer value instead of character. Integer value use less overhead than characters values.If we need to create Index on Char value then use fixed length field instead of Variable Length because index give good performance on CHAR field because it is fixed- length and its length should equal to or less than 10 characters. Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields.

Do Not Use 3- or 4-Part Database Object Names

• Use synonyms to abstract out hard-coded server and database names.  However, we should always include the schema/owner before the database object, even if the owner is DBO.  Schemas should be custom; use of DBO for new implementations needs to be avoided to allow for tighter control over privileges in the database.

Perform Selective Updates

• Don’t update columns that do not need updating, especially if they are part of indexes and foreign keys.

Keep Your SQL as Basic As Possible

• Don’t try to code the entire application in a single SQL statement; it will be difficult to maintain and to debug, and it may not perform as efficiently if it were broken up.  If you are consistently writing SQL with 5+ joins, you may want to consider re-designing the table / tables involved.

Use Functions on Variables, Not on Columns

• Put functions on variables, not on columns; functions on columns prevent the index from being used.  For example, instead of WHERE FUNCTION1(table.column1) = variable1, change the statement so it can be coded like this: WHERE table.column1= FUNCTION2(variable1).

No Query / Index Hints

• Do not use query and index hints without talking and verifying with other members of the team.  Use of optimizer hints is discouraged. It should be justified in each case and explained in comments.

Do Not Add DISTINCT By Default

• Do not automatically add a DISTINCT clause to SELECT statements. There is no need to include a DISTINCT clause by default. If you find that you need it because duplicate data is returned, the duplicate data may be the result of an incorrect data model or an incorrect join. For example, a join of a table with a composite primary key against a table with a foreign key that is referencing only part of the primary key results in duplicate values. You should investigate queries that return redundant data for these problems.

Maintain good formatting in SQL code

• Good formatting of SQL code is essential for making the code readable and maintainable.  An easy solution would be to use an Instant SQL Formatter (such as Notepad++).

No nesting of functions, views, and stored procedures deeper than 1 level

• Nesting (i.e. sproc that calls a view that calls a function that calls a….) prevents SQL Server from displaying complete execution plans.  This makes it difficult to estimate how the code will perform in production and, in the event of poor performance, hinders troubleshooting efforts.

If the query contains more than 10 OR conditions for a single column, populate a temp table variable with search parameters and join on it

• Using multiple OR conditions forces SQL Server to scan an index, instead of performing a seek operation. In situations when there are more than 10 OR conditions, it is faster to first insert the search parameters into a temp table variable and then use it in a join.

No case statements on strings longer than 10 characters

• String operations in SQL Server are slow and very resource intensive.  Using numeric identifiers, instead of text based descriptions will result in faster queries.

Avoid Indexed Views

• Indexed views add significant overhead to DML operations that are impacting any of the tables referenced by the view.  Additionally, indexed views cause frequent deadlocks.

Avoid Using Cursors

• Use of cursors (either T-SQL or programmatically implemented through WHILE loops) is strongly discouraged. Try to find a set-processing solution instead of a loop.

No Triggers

• Use of triggers is prohibited.  Triggers add an overhead to every DML operation that touches the trigger’s parent table.  Additionally, triggers make troubleshooting and maintenance of queries extremely difficult and slow.

• See Data Modeling Standards document for more information.

User-Defined Functions

• User-defined functions are discouraged. They are good for reusing common blocks of code but are often bad for performance for a number of reasons.  If this must be created, avoid running functions on indexed/key columns, as the function will cause the index to be ignored.

Avoid Nested Functions

Nested functions (functions that call functions) prevent SQL optimizer from correctly estimating statistics, which results in super execution plans and poorly performing queries.

Temporary table and Table Valued Function (TVF) practices

• Generally, use of temp tables and TVFs should be avoided on procedures that are called frequently by user initiated requests.  As tempdb is a shared resource, it is often a source of contention as workloads increase.

• Temporary tables must be used with caution (check if there is a way to avoid using them, such as Common Table Expressions).  If there is no other alternative, temporary tables can be used BUT MUST BE DESTROYED at the end of the procedure.

• If temporary tables must be used within a transaction, use table variables instead of “#name” temporary tables.

• If temp tables must be used in user initiated requests, avoid TVFs altogether.  Instead, explicitly create the temp table with a clustered index before inserting data.  The reason for this is that TVFs and temp tables without a clustered index act as storage heaps. Creating and destroying heaps frequently results in contention on special allocation pages in the tempdb database (http://sqlcat.com/sqlcat/b/technicalnotes/archive/2011/01/25/table-valued-functions-and-tempdb-contention.aspx ).

Use Common Table Expressions or Table Variables instead of temp tables

• Where possible, leverage Common Table Expressions (CTEs) or Table Variables instead of using temp tables.  Temp tables & table variables both utilize TempDB and can be overused / abused to the point where the database runs out of TempDB space.  When temp tables cannot be avoided, they should be explicitly created as noted above.

• Overview of each option:
• Temp Tables

• Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that procedure calls.

• Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.

• Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

• Table Variables
• These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.  Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

• Like with temp tables, table variables reside in TempDB. They have entries in the system tables in tempDB, just like temp tables, and they follow the same behavior regarding whether they are in memory or on disk.

• Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimizer has no way of knowing the number of rows in the table variable.

• Table variables used in stored procedures cause fewer re compilations of the stored procedures than when temporary tables are used.

• Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.

• CTE (Common Table Expressions)
• §  CTEs are more like temporary views than anything else. When you look at the execution plan, you'll see that they are in-lined into the query, not materialized and stored. With the exception of recursion, they're used to make queries simpler to write than faster to run.

• §  More details on CTEs: http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

Remove all obsolete/abandoned objects

• A stored procedure or view might become obsolete if the structure of a dependent table or view changes and the table or column references no longer exist.  When this happens the object cannot be recompiled.  Best practice is to refactor these objects if they are in use, otherwise DROP them.

Avoid linked server / cross-database SQL calls

• Linked server connections and cross-database connections should be avoided wherever possible. Primarily these result in slower performance and can become cumbersome when moving databases to different locations.

Circular References

• When an objects in database project A refer to an objects in database project B and vice versa, a circular reference between the database projects exists.  Visual Studio cannot effectively resolve these references at design time and cannot orchestrate a build.  Best practice is to choose which databases queries will execute on and which databases will be used for reference.

Avoid the use of OPENQUERY

• OPENQUERY requires that you provide a linked server name and does not accept variables for its arguments.  For these reasons it is impossible to create an OPENQUERY statement that would be portable from environment to environment.  Consider refactoring this code.

About LIKE Operator

• If you use LIKE in your WHERE clause, try to use one or more leading characters in the clause, if possible. For example, use: ( LIKE  'm%' instead of LIKE ‘%m’)

Avoid dynamic SQL

• Try to find alternatives that do not constitute of dynamic SQL because Dynamic SQL tends to slow than static SQL, as SQL Server generate execution plan every time at runtime. If at all using dynamic SQL, use sp_executesql instead of EXECUTE (EXEC) – Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTES; and because sp_executesql generates execution plan  that are not more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTES

Using the ORDER BY Clause

• Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For example, although a statement such as SELECT ProductID, Name FROM MyTable ORDER BY 2 is valid, the statement is not as easily understood by others compared with specifying the actual column name. In addition, changes to the select list, such as changing the column order or adding new columns, will require modifying the ORDER BY clause in order to avoid unexpected results. For more detail: http://technet.microsoft.com/en-us/library/ms188385.aspx

Using the MERGE Statement

One can implement multiple DML operations instead of writing separate INSERT, UPDATE, DELETE operations. Example is below:

  

Ex: CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);

CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target

MERGE INTO dbo.tbl_Target AS t

    USING dbo.tbl_Source AS s   

        ON t.id = s.id

    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN

       

--Row exists and data is different

        UPDATE SET t.name = s.name, t.qty = s.qty

    WHEN NOT MATCHED THEN

       

--Row exists in source but not in target

        INSERT VALUES (s.id, s.name, s.qty)

    WHEN SOURCE NOT MATCHED THEN

    

    --Row exists in target but not in source

        DELETE OUTPUT $action, inserted.id, deleted.id

Stored Procedure Formatting

Stored Procedures are the core of our applications, and should follow standards to remain consistent across all development efforts.

• Creation Standards
• To improve maintainability, use object owner while referencing an object in the code. E.g. In case of calling from another database, [DatabaseName].[Owner].[TableName] or [DatabaseName].[Owner].[StoredProcedure]. In case of calling from the same database use [Owner].[ObjectName].  The schema/owner must be referenced for every table.

• Use SET NOCOUNT ON at the beginning of every stored procedure.

• Use SCOPE_IDENTITY() instead of @@IDENTITY if the purpose to capture the latest identity after inserting record in specific table in your session.

• SET ANSI_NULL ON

• SET QUOTED_IDENTIFIER ON

• SET ANSI_PADDING OFF

• Don’t create stored procedure with prefix sp_ because this special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly. We can prefix  usp_

• Always use TRY/CATCH for exception handling in Stored Procedure

Use of NO LOCK Discouraged

• Use of NOLOCK (aka READUNCOMMITED) hints is STRONGLY discouraged.  We should not be performing “dirty reads” in our code, and any attempt to do so must get architecture approval.

Comment Block

• Header comments must contain the purpose of the object, author’s name, revision history (date, programmer’s name, and a description of change for every revision), and explain specific conditions that may affect object use (if applicable).

• Stored procedure and UDF parameters require comments explaining their purpose, possible values, and default values (if applicable).

• Comments in the object body should indicate major algorithm steps, explain special conditions and programming techniques employed, and provide other important information for future support of the object.

Code Formatting

• Lines should be typically within 80 characters long (to fit most code editors). Longer lines may be used for long literal strings.

• Indentation should be used to indicate statements within IF…ELSE, and BEGIN…END blocks, format T-SQL statements consisting of multiple clauses (e.g. SELECT queries), and any long statements that need to wrap.

• Statements within BEGIN TRANSACTION and COMMIT TRANSACTION should not be indented relative to surrounding statements, because they do not represent a nested block in the object’s control of execution flow.

• SQL commands (SELECT, FROM, INSERT, etc.) should all be in CAPS.  Any DECLARE, BEGIN, etc. commands should also be in CAPS to improve readability for reserved words.
• Use common sense to make code easier to read and understand.

Do not use RECOMPILE option to stored procedure without valid reason (without discussion with Data arch & DBA team), because it creates a brand new execution plan every time it runs, this causes increased CPU time due to compiling the execution plan over and over.

SQL Code Naming Conventions & Standards

• Use UPPERCASE for T-SQL keywords.

• Use Mixed_Case_With_Underscores for user-defined names.

• Object and column names must be used in exactly the same case in which their names appear in the database.

• Names of programmer-defined element (variables, temporary tables, labels) should consist of words completely and consistently spelled out.

• Abbreviations are to be avoided, except for ones commonly recognized within the Healthcare industry (ICD9 is an example). The same abbreviations, once used, should be applied consistently in all programmer-defined elements.

• Recommended naming prefixes for programmer-defined names are listed in the chart below.  NOTE: All data object naming standards are sync’d with the Data Modeling standards – please reference that document for the most updated definitions.

Object / Prefix	Detail
Schema	Tables do not have a “prefix” per application area but should have a custom schema associated to a group of tables.  Examples that exist today include “REF” for Reference tables, “DIM” for Dimensions, etc.  The default DBO schema should never be used for new data models.  See the Data Modeling Standards document for more information.
vw	View
vx	Indexed View
fn_	Function
PK_	Primary key constraint. Programmer-defined constraint names are preferred to automatically assigned names.
FK_	Foreign key constraint. Programmer-defined constraint names are preferred to automatically assigned names.
DF_	Default constraint. Programmer-defined constraint names are preferred to automatically assigned names.
CHK_	Check constraint. Programmer-defined constraint names are preferred to automatically assigned names.
IX_<UNIQUENESS>	Non-clustered index <UNIQUENESS> should be either U (unique) or N (non-unique)
CIX_<UNIQUENESS>	Clustered index <UNIQUENESS> should be either U (unique) or N (non-unique)
n/t/c/h/b/x [Recommended but not enforced, only for use in T-SQL for naming variables.]	T-SQL variable name prefix indicating data type:
	n	tinyint, smallint, int, bigint, decimal, numeric, float, real, double, money, smallmoney;
	t	smalldatetime, datetime;
	c	char, nchar, varchar, nvarchar;
	h	binary;
	b	bit;
	x	text, image;

Error Handling

• Error handling must be present for all INSERT, UPDATE, and DELETE statements. Check @@ERROR. Take appropriate action if an error is detected.

• Return code must be checked after each EXEC <procedure>. Take appropriate action if return code indicates failure.

• Every procedure and user-defined function must return a non-zero code if it encounters an error.

• The number of RETURN points must be reduced to either one or two (one for success and one for failure).

• Objects must use deadlock-prevention techniques when there is a danger of causing deadlocks: short transactions, minimization of explicit transaction handling, modification to multiple tables done in the same order in all procedures that modify them. (Consult DBA if you want to implement retry logic for transactions in danger of deadlocking.)

• Avoid using SQL Server reserved keywords to name user created object (Table Name, Column Name, Stored Procedure Name, View or Function). For existing user created objects, when they are referenced use square brackets around them so SQL Server do not parse and treat as a keyword.  For example, a column name “USER_ID” - since this is a keyword, it should be referenced as [USER_ID].  A complete list of SQL Server keywords can be found in SQL Server Books Online by searching Reserved Keywords.

Sample Code

An example of a properly formatted (sample) Stored Procedure can be found below:

CREATE PROCEDURE dbo.Code_Review_Example

/*************************************************************************************

** Name:   Code_Review_Example

**

** Description:   This procedure demonstrates recommended coding style.  It has no

** other purpose. Some of the parts may be meaningless.

**

** Return Code: 0 - Success, -1 - Failure

** USAGE: EXEC Code_Review_Example @nSalvage_ID = 23000, @bClose_Ind  = 1

** ACTIONS: S – I – U – D;     S: Select, I: Insert, U: Update, D: Delete

**

** -----------------------------------------------------------------------------------

** Revision History:

** -----------------------------------------------------------------------------------

** Date:                          Author                      Description

** -----------------------------------------------------------------------------------

** 12 Dec 2012            Bilbo Baggins           Original version     

** 24 Jan 2013            Tyler Durden            Added error control

** 27 Feb 2013            Kaiser Soze             Updated comments and business logic

**

*************************************************************************************/

AS

 BEGIN

 BEGIN TRY     

 @nSalvage_ID INT,    -- The salvage for which the the contract is built

 @bClose_Ind  BIT = 0 -- Close contract indicator (0=no/1=yes)

SET NOCOUNT ON

-- Declare many variables in a single DECLARE statement for performance:

DECLARE @tContract_Update_DateTime   DATETIME,

        @cAgreement_Term_Method_Code VARCHAR(20),

        @cAgreement_Term_Type_Code   CHAR(3),

        @bPrebillable_Ind            BIT,

        @nFee_Amount                 MONEY,

        @nFee_Percentage             DECIMAL(7,2),

        @nMessage                    VARCHAR(200),

        @nError                      INT,

        @nRowCount                   INT

--------------------------------------------------------------------------------

-- Save frequently used row sets in temporary tables to avoid joining the same

-- tables many times:

-- @#Contract (it may be Temp Table or Table Variable)

DECLARE @#Contract TABLE (

        Agreement_Term_Type_Code            CHAR(3)  NOT NULL,

        Advance_Charge_Prebill_Approved_Ind BIT      NOT NULL,

        ReRun_Charge_Threshold              SMALLINT NOT NULL,

        Hybrid_Agreement_Ind                BIT      NOT NULL,

        Rate_Group_Criteria_ID              INT      NULL,

        Sequence_Number                     SMALLINT NOT NULL

                                            PRIMARY KEY CLUSTERED

)

INSERT INTO @#Contract

SELECT        ISNULL( sci.Agreement_Term_Type_Code, '' ),

              rg.Advance_Charge_Prebill_Approved_Ind,

              rg.ReRun_Charge_Threshold,

              ISNULL( sci.Hybrid_Agreement_Ind, 0 ),

              scii.Rate_Group_Criteria_ID,

              scii.Sequence_Number

FROM          [dbo].[Salvage_Contract_Info] AS sci

JOIN          [dbo].[Salvage_Contract_Info_Item] AS scii

ON            sci.Salvage_Contract_Info_ID = scii.Salvage_Contract_Info_ID

AND           sci.Other_ID = scii.Other_ID

JOIN          [dbo].[Rate_Group] AS rg

ON            scii.Rate_Group_ID = rg.Rate_Group_ID

WHERE         sci.Salvage_ID = @Salvage_ID

ORDER BY      scii.Sequence_Number

SELECT @nError = @@error, @nMessage = 'Failed to insert @#Contract'

IF @nError <> 0 GOTO Failed

-- If free tow is indicated, insert a free_ind row for each hauling charge:

IF @Free_Ind = 1 BEGIN

        SELECT  @nSalvage_ID,

                c.Charge_Type_ID,

                ISNULL( o.Charge_Basis_Code, '' ),

                Salvage_Contract_ID =

                        CASE WHEN ISNULL( o.IAA_Rate_Group_Ind, -1 ) <> 0

                             THEN 0

                             ELSE o.Salvage_Contract_ID

                        END,

                o.Rate_Group_ID,

                p.Prebill_Enhancement_Ind,

                @cEnd_Event_Code,

                Note_Text           = '',

                Free_Ind            = 0,

                Max_Amount          = NULL,

                Period_Days  = NULL,

                Contract_Update_DateTime = o.Update_DateTime,

                Contract_FK_Type    = 2,

                Contract_FK         = o.Rate_Group_Enhancement_ID

        FROM  ( SELECT  Charge_Type_ID,

                        Prebill_sort_id = MIN( sort_id ),

                        Other_sort_id   = MIN( CASE WHEN Charge_Basis_Code = 'IA'

                                                    THEN NULL

                                                    ELSE sort_id

                                               END )

                FROM    @#Contract

                GROUP BY

                        Rate_Group_Criteria_ID

              ) c

        left outer join @#Contract p on p.sort_id = c.Prebill_sort_id

        SELECT @nError = @@ERROR, @nMessage = 'Failed to select'

        IF @nError <> 0 GOTO Failed

END

--------------------------------------------------------------------------------

-- Process charges and check return code:

EXEC @nError = Process_Contract_Charges @nSalvage_ID

SET @nMessage = 'Failed to execute Process_Contract_Charges'

IF @nError <> 0 GOTO Failed

RETURN 0    -- SUCCESS

--------------------------------------------------------------------------------

-- Common error handing block:

FAILED:

RAISERROR( 'Code_Review_Example ERROR: %s. @@ERROR=%d.',

                10, 1, @cMessage, @nError )

RETURN -1    -- FAILURE

--------------------------------------------------------------------------------

-- Destroy Temp table when processing is complete:

DROP TABLE @#Contract; -- if its Table Variable then don't need to distroy manually

END TRY

BEGIN CATCH

SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() as ErrorState,

        ERROR_PROCEDURE() as ErrorProcedure,

        ERROR_LINE() as ErrorLine,

        ERROR_MESSAGE() as ErrorMessage;  

END CATCH

END