/****** Object: StoredProcedure [dbo].[SPInsertCollectionCallDetails] Script Date: 02/23/2016 11:14:04 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
— Author: <Hari Ere>
— Create date: <01/17/2014>
— Description: This procedure input the package process steps
— Excute SP
DECLARE @return_value int
EXEC @return_value = dbo.spinsert
@id = 1,
@ProcessStepInfo = NULL,
@Status = NULL
SELECT ‘Return Value’ = @return_value
— code here
SELECT ‘hello world’
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();
EXEC sp_send_dbmail @profile_name = ‘Mail_ Profile’,
@recipients = ‘email@example.com’,
@subject = ‘Error from procedreuname ‘, @body = @ErrorMessage;
If code required in Transaction, please make sure SET XACT_ABORT ON(rolls back the current transaction when a Transact-SQL statement raises a run-time error )
SET XACT_ABORT ON;
TSQL best practices.
- To improve code readability, use proper indention for the statements.
- To understand code easily, please add comments with each begging of the logic.
- Use CAPS for all Sql Server Keywords ( Ex: SELECT,ORDER BY).
- Use SET NOCOUNT ON option at the top of procedure to avoid messages.
- Do not use SELECT * all columns, instead specific column names in TSQL.
- Write object names with fully qualified names.(Databasename.schema.objectname).
- SYNONYMs provide a layer of abstraction over the referenced object with a simplified alias as a same server resident object.
- Declare variables in required size, which eliminates consuming memory buffers.
- Never create stored procedure name with SP, SP is reserved for system SQL server.
- Try to avoid using cursors, use While loops.
- Create views if the quires are complex.
- Avoid sub quires and use CTE, CTE’S are reside on memory for faster access.
- Use ORDER BY,DISTINCT,TOP only when required.
- Avoid the temp table in the stored procedure. Stored procedures are usually use a cached execution plan to increase the performance. When you use the temp table it will do the compilation every time.
- Use the sp_executesql stored procedure instead of the EXECUTE statement.(ex: EXECUTE sp_executesql @Query)
- Keep the transaction as short as possible, with SET XACT_ABORT ON. This will roll back when error raise.
- USE TRY CATCH BLOCK to trap the errors, and send alerts.
- Use table variables inside the SP, for small datasets. If the data sets are larger, use temp tables and add index for better performance.
- Create backup tables into a separate database, it will help DBA’s for cleaning.
- To avoid deadlocks Always access tables in the same order in all your Stored Procedures and triggers consistently, keep your transactions as short as possible.
Never, ever wait for user input in the middle of a transaction.
- Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime.
- Dropping a temp table
IF OBJECT_ID(‘tempdb..#CpuCounters1’) IS NOT NULL
DROP TABLE #CpuCounters1