A Stored
Procedure is a set of Structured Query Language (SQL) statements with an
assigned name, stored in the database in compiled form so that it can be
shared by a number of programs.
CREATE Procedure
Creating a new Stored Procedure using the CREATE PROCEDURE command as given below.
Stored Procedure consists of :
·
Header comments, Which includes author, date, purpose and
change history with example.
·
Procedure Name.
·
Input parameters, Output parameters if any.
·
Body of Stored Procedure
1. BEGIN
2. SET NOCOUNT
ON;
3. DECLARE Local
variables
4. T-SQL [Collection
of SQL Statements]
5. SET NOCOUNT OFF;
6. END
EXAMPLE:
/*======================================================================
AUTHOR
: Govind
CREATE DATE :
22-Aug-2014
DESCRIPTION :
Logging Error.
SNO ALTERED DATE AUTHOR DESCRIPTION
--- ---------------------- ------------- ----------------------------------------------------------------
1 09-APR-2015 Govind Changed
Condition with @@TRANCOUNT
Instead
of @ERROR
2 25-JUN-2015 Govind Added
additional parameter
@ErrorLogMethodName
@ErrorLogMethodName
SET STATISTICS TIME ON
EXEC [USP_PP_ErrorLog_Insert] 1, '1', 23, 'This is Error due to invalid
data', 'Action Table', 'Action.js','getAction'
SET STATISTICS TIME OFF
=====================================================================*/
CREATE PROCEDURE
[dbo].[USP_PP_ErrorLog_Insert]
--
Parameters for the stored procedure
@OperatorID INT,
@SystemMessageID INT = 0,
@ErrorLogLineNumber INT,
@ErrorLogMessage VARCHAR(1000) = null,
@ErrorLogFunction VARCHAR(50) = null,
@ErrorLogClassName VARCHAR(30) = null,
@ErrorLogMethodName VARCHAR(30) = null
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN
TRANSACTION
INSERT INTO [dbo].[ErrorLog]
([ErrorLogDate]
,[SystemMessageID]
,[ErrorLogMessage]
,[OperatorID]
,[ErrorLogFunction]
,[ErrorLogClassName]
,[ErrorLogMethodName]
,[ErrorLogLineNumber])
VALUES
(GETDATE()
,@SystemMessageID
,@ErrorLogMessage
,@OperatorID
,@ErrorLogFunction
,@ErrorLogClassName
,@ErrorLogMethodName
,@ErrorLogLineNumber)
COMMIT TRANSACTION
RETURN -- 'SUCCESS'
END TRY
BEGIN CATCH
IF @@TRANCOUNT
> 0 --
@@ERROR>0
ROLLBACK
SELECT
@ErrorLogLineNumber = ERROR_LINE(),
@ErrorLogFunction
= ERROR_PROCEDURE(),
@ErrorLogMessage = CAST(ERROR_NUMBER() AS VARCHAR(15)) + '-' + ERROR_MESSAGE()
INSERT
INTO [dbo].[ErrorLog]
([ErrorLogDate]
,[SystemMessageID]
,[ErrorLogMessage]
,[OperatorID]
,[ErrorLogFunction]
,[ErrorLogClassName]
,[ErrorLogMethodName]
,[ErrorLogLineNumber])
VALUES
( GETDATE()
,@SystemMessageID
,@ErrorLogMessage
,@OperatorID
,@ErrorLogFunction
,@ErrorLogClassName
,@ErrorLogMethodName
,@ErrorLogLineNumber)
--SELECT @OutputText
AS ErrorCode
RETURN --'ERROR'
END CATCH
SET NOCOUNT OFF;
END
ALTER Procedure
Example for modifying Stored Procedure using the ALTER PROCEDURE command is given below.
ALTER PROCEDURE [dbo].[USP_PP_ErrorLog_Insert]
EXECUTE Procedure
Executing Stored Procedure consists of:
EXEC <<Procedure Name>> <<Parameters>>
EXAMPLE:
EXEC [USP_PP_ErrorLog_Insert] 1, '1', 23, 'This is Error due to
invalid data', 'Action
Table', 'Action.js','getAction'
DROP Procedure
Example for deleting the existing Stored Procedure using the DROP PROCEDURE command is given below.
EXAMPLE1:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_PP_ErrorLog_Insert]') AND type in (N'P', N'PC'))
EXAMPLE1:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_PP_ErrorLog_Insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_PP_ErrorLog_Insert]
GO
RENAME Procedure
Example for renaming the existing Stored Procedure (from USP_PP_ErrorLog_Insert to USP_PP_ErrorLog_Upsert) is given
below.
EXAMPLE:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_PP_ErrorLog_Upsert]') AND type in (N'P', N'PC'))
AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_PP_ErrorLog_Insert]') AND type in (N'P', N'PC'))
EXEC sp_rename 'USP_PP_ErrorLog_Insert',
'USP_PP_ErrorLog_Upsert'
Another way of renaming the existing Stored Procedure (from
USP_PP_ErrorLog_Insert to USP_PP_ErrorLog_Upsert) is, CREATE a new Stored Procedure, and then DROP the
existing Stored Procedure as given below.
EXAMPLE:
CREATE PROCEDURE [dbo].[USP_PP_ErrorLog_Upsert]
……….
……….
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_PP_ErrorLog_Insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_PP_ErrorLog_Insert]
GO
General Rules:
·
Follow the naming conventions.
·
Do not use spaces in the name of database objects.
·
Write neat, readable and meaningful comments wherever needed.
o
Use -- for single line comment
o
Use /* ……*/ for block comments.
·
Do not use SQL keywords as the name of database objects, In cases
where this is necessary, surround the object name with brackets, such as [Year].
Coding:
Good database design and coding will achieve all the required
project functionality and deliver acceptable performance. It takes into quality
delivery to your customer.
·
Optimize queries
using the tools provided by SQL Server.
Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your
queries do an "Index seek" instead of an "Index scan" or a
"Table scan." A table scan or an index scan is a highly undesirable
and should be avoided where possible.
EXAMPLE
1:
IF EXISTS (SELECT 1 FROM
dbo.Action WITH (NOLOCK) WHERE
ActionID = 50)
Instead Of:
IF EXISTS (SELECT COUNT(ActionID) FROM dbo.Action WHERE
ActionID = 50)
EXAMPLE
2:
Consider the following query to
find the second highest offer MaxManPayAmount from the Operator table:
SELECT MIN(MaxManPayAmount) FROM Operator WHERE OperatorID IN
(SELECT
TOP 2 OperatorID FROM Operator ORDER BY MaxManPayAmount Desc)
The same query can be re-written
using a derived table, as shown below, and it performs generally as fast as the
above query:
SELECT MIN(opr.MaxManPayAmount) FROM
(SELECT
TOP 2 MaxManPayAmount FROM dbo.Operator WITH (NOLOCK) ORDER BY
MaxManPayAmount DESC) AS opr
·
Do not use SELECT *, instead practice writing required column names
after the SELECT statement.
·
Do not use SQL keywords as the name of database objects, In cases
where this is necessary, surround the object name with brackets, such as [Year].
·
Use table WITH
(NOLOCK) command for SELECT statement which avoids a
deadlock occurrence.
·
Avoid dynamic SQL
statements as much as possible.
o
Dynamic
SQL tends to be slower than static SQL, as SQL Server must generate
an execution plan at runtime. IF and CASE statements come in handy to avoid
dynamic SQL.
·
Avoid using <> as
a comparison operator.
o
Use ID IN (1,3,4,5)
instead of ID <> 2.
·
Avoid unnecessary use of temporary tables.
o
Use 'Derived tables' or CTE
(Common Table Expressions) wherever possible, as they perform better.
·
Use SET NOCOUNT ON at
the beginning of stored procedures, SQL batches and Triggers. This improves the
performance of stored procedures.
o
SET
NOCOUNT ON suppresses messages like '(1 row(s) affected)' after executing
INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to
the reduction of network traffic.
·
Place all DECLARE
statements before any other code in the stored procedure.
·
Do not use cursors or application loops to do inserts.
o
Try to avoid server side cursors as much as possible. Always stick
to a 'set-based approach' instead of a 'procedural approach' for accessing and
manipulating data. Cursors can often
be avoided by using SELECT statements instead. If a cursor is unavoidable, use
a WHILE loop instead. For a WHILE
loop to replace a cursor, however, you need a column (primary key or unique
key) to identify each row uniquely.
·
Fully qualify tables and column names.
o
Prefix table names with the owner name. This improves readability
and avoids unnecessary confusion. Microsoft SQL Server Books Online states that
qualifying table names with owner names help in execution plan reuse, further
boosting performance.
Example: dbo.FeatureAction
·
Use ANSI 92 Joins instead
of old style joins his improves readability and avoids unnecessary confusion.
False code:
SELECT * FROM dbo.FeatureAction,
Feature
WHERE
Feature.FeatureID =
FeatureAction.FeatureID
True code:
SELECT FeatureAction.FeatureID, Feature.FeatureName FROM dbo.FeatureAction
INNER JOIN dbo.Feature WITH (NOLOCK) ON Feature.FeatureID = FeatureAction.FeatureID
·
Avoid the use of cross
joins, if possible.
·
Avoid to define default
values for parameters in stored procedure unless or until it is needed.
o
If a default is needed, the front end will supply the value.
·
Do not use the RECOMPILE
option for stored procedures.
·
Do not use column numbers in the ORDER BY clause. Use appropriate column name.
·
Do not use GOTO.
·
Check the global variable @@ERROR
immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can
rollback the transaction if an error occurs OR Use TRY/CATCH.
·
Create STORED PROCEDURE
as much as possible with business logic on the same database for accessing data.
o
Do not prefix stored procedures with ‘sp_’, prefix.
o
The prefix sp_ is
reserved for system stored procedures that ship with SQL Server. Whenever SQL
Server encounters a procedure name starting with sp_, it first tries to locate
the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo
as the owner. Time spent locating the stored procedure can be saved by avoiding
the "sp_" prefix.
o
Do prefix stored procedures with “USP_”.(user defined stored
procedure)
·
Prefix table names with the owner name.
o
Example: dbo.Action instead
of Action
·
You might need the length of a string variable in many places of
your stored procedure, but don't call the LEN function whenever it's needed.
Instead, call the LEN function once and store the result in a variable for
later use.
· If Multiple records to be
stored /deleted to table at an attempt, pass XML data as parameter value to stored procedure for BULK INSERT OR BULK UPDATE OR BULK DELETE.
XML data should be created/formed from application server and pass it to xml.
Example: Bulk Insert.
DECLARE @XML
AS XML
--SET @XML =
(select * from ED for xml auto)
SET @XML
= '<ED
EID="1" DID="1" SN="1"/>
<ED
EID="1" DID="2" SN="2"/>
<ED
EID="1" DID="3" SN="3"/>
<ED
EID="2" DID="4" SN="1"/>
<ED
EID="2" DID="5" SN="2"/>
<ED
EID="3" DID="7" SN="2"/>
<ED
EID="4" DID="7" SN="1"/>
<ED
EID="4" DID="8" SN="2"/>
<ED
EID="5" DID="4" SN="1"/>
<ED
EID="5" DID="5" SN="2"/>
<ED
EID="5" DID="6" SN="3"/>
<ED
EID="6" DID="7" SN="1"/>
<ED
EID="6" DID="8" SN="2"/>
<ED
EID="6" DID="9" SN="3"/>
<ED
EID="7" DID="0" SN="1"/>'
INSERT INTO EmployeeDegree
SELECT
DOC.col.value('@EID', 'INT') as EmployeeID,
DOC.col.value('@DID', 'INT') as DegreeID,
DOC.col.value('@SN', 'INT') as Sequence
FROM @XML.nodes('ED') DOC(col)
·
Access tables in the same
order in your stored procedures and triggers consistently.
·
Do not call functions
repeatedly within your stored procedures, triggers, functions and batches.
·
The RETURN statement is
meant for returning the execution status only, but not data.
·
Return multiple result sets
from one stored procedure to avoid trips from the application server to SQL
server.
Query the list of Stored Procedure
EXAMPLE:
SELECT * FROM sysobjects
WHERE xtype = 'P' --'TR', FN, 'P', 'V'
EXAMPLE:
SELECT DISTINCT so.name, so.xtype, sc.TEXT
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Chk%'
AND so.xtype IN ('P', 'TR', 'FN', 'P', 'V')
No comments:
Post a Comment