How to do error handling in Stored Procedures?

Create PROCEDURE [dbo].[usp_InsertContactInformation]
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY--Begin try
BEGIN TRANSACTION
/*
SET @ErrorTrackMessage='TrackPoint'
--We are going to use this @ErrorTrackMessage in catch block if something not caught in transaction Block.
IF @Error_No <> 0
BEGIN
SELECT CONVERT(VARCHAR,@Error_No)+',Error in Procesing the file' as Message
ROLLBACK TRANSACTION
--Perform Necessary Opeartion
RETURN
END --End of IF
ELSE
BEGIN
IF @@ERROR = 0
BEGIN
SET @ErrorTrackMessage = 'All Tranactions Completed'
END --End of IF
COMMIT TRANSACTION
SELECT 'Pass' as Message
--Perform Necessary Opeartion
RETURN
END--End of Inner Else
*/
END TRY--End Try
BEGIN CATCH--Begin Catch
/*
IF @ErrorTrackMessage <> 'TrackPoint'
BEGIN
SELECT CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),'')) + ',' + CONVERT(VARCHAR,ISNULL(ERROR_MESSAGE(),'')) + ' in ' + CONVERT(VARCHAR,ISNULL(ERROR_PROCEDURE(),'')) + ' at Line number ' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(),''))+ ' After ' + @ErrorTrackMessage
END
ELSE
BEGIN
SELECT CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),'')) + ',' + CONVERT(VARCHAR,ISNULL(ERROR_MESSAGE(),'')) + ' in ' + CONVERT(VARCHAR,ISNULL(ERROR_PROCEDURE(),'')) + ' at Line number ' + CONVERT(VARCHAR,ISNULL(ERROR_LINE(),''))+ ' After ' + @ErrorTrackMessage +
'While Executing Logic'
END
ROLLBACK TRANSACTION
--Perform Necessary Opeartion
*/
END CATCH--End Catch
END

How to retriev table data in xml format?

We can retriev table data in xml format by using For xml 4 different modes.
Please see the below examples for reference.


Output of the above script

Output of the above script

Output of the above script

How to read attributes of a xml type?

How to Parse XML data using XML type?

We can parse xml data by using .query() method or by .value() method.
XML type is having 2 more methods
1.nodes()->This method will return each node of xml file.
2.exist()->This method is quite similar to IF Exists method of sql server.

What is XML Type in SqlServer?

Xml Type is new datatype introduced in Sqlserver 2005 and SQlserver 2008.
Before Xml type, text type was used for storing Xml data but there was limitation of xml size.
XMl Type: Now new xml type can hold xml file upto 2GB.

What is Merge Operation in SqlServer 2008?

Many Times while writing Sql Statement we use IF ELSE Statement ,IF EXISTS Statement & IF NOT EXISTS Statement and on the basis of found result we update ,Delete Or Insert the DataBase table.In that case we have to write number of lines.
In SqlServer 2008 Microsoft has provided MERGE statement which is not only simple but also have high performance.
The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.
e.g. A School is conducting a Test for taking admission in new Batch and after Entrance Examination School Authority has defined some rule for rejection/selection of students .
Rules.
1. Delete All Records From The Student Table Where Marks < 30
2. Insert All the Records in The Student Table where Marks >=30 if not exists in the Student Table
3. Update the Result column of Student Table as Pass if Marks >30
Table ScreenShot
--Try to Implement the logic for the same as you are doing till now in sqlserver 2005 and than compare the effort and performance from below code using Merge Statement.
Using Merge Statement:

After Execution of the below Merge statement final output looks like as below.

What is Table Valued Parameter in SqlSever 2008?

Sample Code From Database Front

Create Tabel Structure in Database

Database view

--Traditional Approach for Creating Insert SP
-- =============================================
-- Author: Bimlesh Singh
-- Create date: 05th June 2011
-- Description: Stored Procedure without Table Valued Parameter
-- =============================================
CREATE PROCEDURE usp_Insert
-- Add the parameters for the stored procedure here
@ID int ,
@Name nchar(10),
@Address_Line 1 nchar(10),
@Address_Line 2 nchar(10),
@Pin Code int
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO Student VALUES (@ID,@Name,@Address_Line 1,@Address_Line 2,@Pin Code)
END
GO

--As per below screenshot we can see multiple times we call the same SP for Insertion

--TVP(Table Valued Parameter) Approach for Creating SP
-- =============================================
-- Author: Bimlesh Singh
-- Create date: 05th June 2011
-- Description: Stored Procedure without Table Valued Parameter
-- =============================================
CREATE PROCEDURE usp_Modified_Insert
-- Add the parameters for the stored procedure here
@StudentType Student Type
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO Student SELECT * FROM @StudentType
END
GO

--From below screenshot we can see we need just the refernce of TVP for Insertion and i.e OneTime effort.

Best SQL Practices

/*
**Please See Example at same Numbering Position below.
**1. Avoid selecting all fields.Select Only those which are required
**2. Avoid selecting filters in select statement
**5. Not operator Prevents From Indexing, so try to avoid it.
**6. Try to Avoid using 'Like' Operator always prefer 'In' and 'Between' Operator
**7. Functions and arithmetic applied against columns with the WHERE Clause means no chance of indexes
--. and repeated execution of the function or arithmetic.
**8. 'Union' returns distinct value where as 'Union all' returns all value including duplicate values.
**9. Avoid using complex Joins it decreases performance.
--The maximum number of tables that can be joined > 100, the practical limit is &lt; 10.
**
*/
--1.
SELECT * FROM dbo.TablName--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName--Good Practice
--2.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT Roll_No FROM dbo.TablName WHERE ID=2--Good Practice becuse It Decreases Overhead
--3.
SELECT DISTINCT Roll_No FROM dbo.TablName --If not Necessary don't use DISTINCT it added one more step for deleting duplicate records.
--4.
--If we are Comparing 2 column keep the dataType Size same
----Because it Increases DataType Conversion Overhead Unnecessary
--e.g EmpId(varchar 50)of a Table Compare with EmpId(varchar 20) of another Table
--5.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID Not Between 1 and 3--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName WHERE ID '>' 3--Good Practice
--6.
--If you want to search all Name like Bikesh,Bimlesh,Bikash i.e BIM to BIK
SELECT [Name] FROM dbo.TablName WHERE [Name] LIKE 'B%'--Bad Practice
--In the above statement it will check whole data
SELECT [Name] FROM dbo.TablName WHERE [Name] Between 'Bi'and 'Bj'
--In the above statement it will check only data starting from 'BI' to 'BJ'
--7.
SELECT Id FROM dbo.TablName WHERE ID + 1= 10--Bad Practice
SELECT Id FROM dbo.TablName WHERE ID = 10-1--Good Practice
--8.
--. Wanna to check if a Table is Blank or Not
SELECT count(*) FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT top 1 [Name] FROM dbo.TablName WHERE ID=2--Good Practice
--9.
--Q.1 Find all the Name of Students whose Number lies between 1 to 33 if found Than say Fail
--Q.2 Find all the Name of Students whose Number lies between 34 to 55 if found Than say IInd division
--Q.3 Find all the Name of Students whose Number lies between 55 to 100 if found Than say Ist division

--BAD Approach:
SELECT [Name] FROM dbo.TablName WHERE Number Between 1 and 33
Print 'Fail'
SELECT [Name] FROM dbo.TablName WHERE Number Between 33 and 55
Print 'IInd Division'
SELECT [Name] FROM dbo.TablName WHERE Number Between 55 and 100
Print 'Ist Division'

--GOOD Approach:
SELECT [Name]
CASE WHEN Number BETWEEN 1 AND 33
THEN Print 'Fail'
CASE WHEN Number BETWEEN 33 AND 55
THEN Print 'IInd Division'
CASE WHEN Number BETWEEN 55 AND 100
THEN Print 'Ist Division' ELSE Print 'Fail' END,
FROM TablName;

How To Insert XML file in DataBase Using OpenXML

DECLARE @idoc int
DECLARE @doc varchar(1000)

--Create a Temporary Table
Create Table #TempTable([Name] VARCHAR (100),Age VARCHAR (100), Address VARCHAR (100),Hobby VARCHAR (100),BANKCODE VARCHAR (100),BANKACCT VARCHAR (100))

--Assignment of Xml data in a string/xml dataType E.G DECLARE @doc varchar(1000)/DECLARE @doc xml
SET @doc ='Please Refer to Attached Image For XMl Data'

-- Create internal DOM representation of the XML document.
--This Will return a XMlDocument Object which can be iterated with the help of X-Path Query
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--Inserting Data into Temporary Table #TempTable
Insert into #TempTable
SELECT * FROM
--Reading @idoc with OPENXML
OPENXML (@idoc, 'Verification/PersonalDetail', 2)
WITH ([Name] VARCHAR (100),Age VARCHAR (100), Address VARCHAR (100),Hobby VARCHAR (100),BANKCODE VARCHAR (100),BANKACCT VARCHAR (100))

--Removing Compiled Xml Document Object from memory
EXEC sp_xml_removedocument @idoc

SELECT * FROM #TempTable
DROP TABLE #TempTable