best practices for Store Procedure

Using a proper and consistent naming scheme

    dbo.Customer_Create 
    dbo.Customer_Update 
    dbo.Customer_Delete 
    dbo.Customer_GetList 
    dbo.Customer_GetDetails

Lining up parameter names, data types, and default values

CREATE PROCEDURE dbo.User_Update

   @CustomerID     INT,

   @FirstName      VARCHAR(32)     = NULL,

   @LastName       VARCHAR(32)     = NULL,

   @Password       VARCHAR(16)     = NULL,

   @EmailAddress   VARCHAR(320)    = NULL,

   @Active         BIT             = 1,

   @LastLogin      SMALLDATETIME   = NULL

AS

BEGIN

Using SET NOCOUNT ON

This prevents DONE_IN_PROC messages from needlessly being sent back to the client after every row-affecting statement, which increases network traffic and in many cases can fool applications into believing there is an additional recordset available for consumption.

PARAMETER SNIFFING: 

Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries.

Use table variables

Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible.

Use of Temp Tables

If you think that the temporary record set may exceed upto millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.