Exception Handling in SQL Server 2005

Exception Handling in 2000:

In SQL Server 2000 Exception handling was implmented with the help of @@ERROR statement. Each and every statement you have to check the value of Error number. Practically it is not easiest things to do.

Exception Handling in 2005:

In .NET programming exception are handled with help of TRY - CATCH block. Similarly TRY - CATCH block is implmented in SQL Server 2005. When you are exceuting the procedure if error occured then catch block statements are exceuted. This technique is applicable only for the run time errors.

The following functions are used to get the error details

ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_PROCEDURE()
ERROR_LINE()
ERROR_MESSAGE()

Example:

CREATE PROCEDURE PROCEXCEPHANDLING

AS

BEGIN

BEGIN TRY

SELECT 100/0

END TRY

BEGIN CATCH

Select

ERROR_NUMBER() AS ErrNo,

ERROR_SEVERITY() AS ErrSev,

ERROR_STATE() AS ErrState,

ERROR_PROCEDURE() AS ErrProc,

ERROR_LINE() AS ErrLine,

ERROR_MESSAGE() AS ErrMsg

END CATCH

END

Executing the above procedure

Exec PROCEXCEPHANDLING

Output of above statement is

PIVOT function in SQL Server 2005

PIVOT:

This is one of the most important enhancements to TSQL in SQL 2005 and is extremely useful for generating reports based on cross tab values.The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way.


The syntax for the PIVOT relational operator is:


FROMtable_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN (<column_list>)
) table_alias


table_source : The table where the data will be pivoted.
aggregate_function : The aggregate function that will be used against the specified column. pivot_column : The column that will be used to create the column headers.
column_list : The values to pivot from the pivot column.


For Example:


create table parameter
(
pid int,
pname varchar(50)
)
GO


create table TransactionInfo
(
TransId int,
pid int,
pvalue int
)
GO


insert into parameter values(1,'Para1')
GO
insert into parameter values(2,'Para2')
GO
insert into parameter values(3,'Para3')
GO
insert into parameter values(4,'Para4')
GO
insert into parameter values(5,'Para5')
GO

insert into TransactionInfo values(1,1,30)
GO
insert into TransactionInfo values(1,2,40)
GO
insert into TransactionInfo values(1,3,50)
GO
insert into TransactionInfo values(1,4,35)
GO
insert into TransactionInfo values(1,5,45)
GO
insert into TransactionInfo values(2,1,50)
GO
insert into TransactionInfo values(2,2,60)
GO
insert into TransactionInfo values(2,3,70)
GO
insert into TransactionInfo values(2,4,85)
GO
insert into TransactionInfo values(2,5,45)
GO


In TransactionInfo table row wise records are stored. If you need columnwise report we go for PIVOT functions.

select TransID,para1,para2,para3,para4,para5 from
(select a.pname,b.pvalue,TransID from parameter a,TransactionInfo b
where a.pid = b.pid and TransID in (1,2) ) as aa
pivot
(
sum(pvalue)
for pname in([para1],[para2],[para3],[para4],[para5])
) as bb
GO


The Following result set is obtained after executing the above query, which PIVOTS data based on the parameters thats been selected.


Output of above query is as follows

SQL Server 2005 - INTERSECT and EXCEPT operands.

INTERSECT and EXCEPT operands:


Like Union and Union all SQL Server 2005 introduces INTERSECT and EXCEPT operands.

INTERSECT :

It is useful for retrieving only distinct rows that exist in both the left and right queries.

EXCEPT :

It is useful for retrieving only distinct rows that exist in the left query.

For example :

Create table DeptA

( DeptId int, DeptName varchar(40))

GO

Create table DeptB

( DeptId int, DeptName varchar(40))

GO

insert into DeptA values(10,'Sales')

GO

insert into DeptA values(20,'Service')

GO

insert into DeptA values(30,'Puchase')

GO

insert into DeptB values(10,'Sales')

GO

insert into DeptB values(20,'Service')

GO

insert into DeptB values(30,'Marketing')

GO

--Intersect

select * From DeptA

Intersect

Select * From DeptB

GO

--Except

select * From DeptA

Except

Select * From DeptB

GO

Step by Step procedure of CLR Integration in SQL Server 2005

CLR integration in SQL Server 2005:


In T-SQL impossible when we need a stored procedure with some array-operations, text-parsing or complex math operations. However, it is quite easy if our Server allows us to deploy C# or VB.NET code that is used within the Server process. In other words, if we need complex procedural code, we can write it as managed code.


SQL Server 2005 we can write store procedure, triggers,user defined functions , user defined types etc in managed code(CLR) also. It acts as the operating system for the CLR when it is hosted inside SQL Server.


Procedure for Creating CLR integrated Store Procedure in SQL Server 2005 using Visual Studio 2005:

Open Visual Studio 2005 IDE.
· Go to menu File -> New -> Project.
· Within the “New Project” dialog box, select “database” (VB) as the project type and select “SQL Server Project” as the template. Provide your own project name, choose your own location and finally click OK.




You will be prompted with the “New Database Reference” dialog, where you need to provide the instance name of your SQL Server 2005 with database information.



When you are prompted for “SQL/CLR debugging” press “YES”.


Go to the Solution Explorer, right click on project and go to Add -> Stored Procedures




Select “Stored procedure” as in the template, provide the name of the stored procedure as “StoreProcedure1” and finally click add.





Add your code like this


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures1

Public Shared Sub SP_DisplayEmp()
Dim Con As New SqlConnection("context connection=true")
Try
Con .Open()
Dim Cmd As New SqlCommand("select eid,ename,sal from emp", Con)
Dim oReader As SqlDataReader
oReader = Cmd .ExecuteReader()
SqlContext.Pipe.Send(oReader)
Catch ex As Exception
SqlContext.Pipe.Send(ex.ToString())
End Try
End Sub

End Class


Here SqlContext.Pipe statement is an sql ouput statement.
After this ReBuild and Deploy the project through solution explorer.

Now new procedure named "SP_DisplayEmp" is added in master database. This procedure retrieve all the data which available in Emp table.


Open SQL server 2005 Query wizard and execute the following statements


use master
GO
--To enable SQL Server 2005 for CLR integration
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
create table emp
(
eid varchar(10),
ename varchar(40),
sal int
)
GO

insert into emp values('E01','Crys',81000)
GO
insert into emp values('E02','Jhon',72000)
GO
insert into emp values('E03','Gogul',90000)
GO

execute SP_DisplayEmp
GO


CLR integrated Procedure Ouput is

ROLLUP in Sql Server

it is used to Summarizing Data..

The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator.

ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Checkout this example..

Create Table Student
(
StudentID int,
Student_Name Varchar(15),
SubjectName Varchar(15),
Marks_Scored numeric(18,1)
)

Insert into Student Values(1,'Babu', 'Tamil',90)
Insert into Student Values(1,'Babu', 'English',78)
Insert into Student Values(1,'Babu', 'Maths',88)
Insert into Student Values(1,'Babu', 'Science',88)
Insert into Student Values(1,'Babu', 'Social Science',92)

Insert into Student Values(2,'Ramesh', 'Tamil',92)
Insert into Student Values(2,'Ramesh', 'English',88)
Insert into Student Values(2,'Ramesh', 'Maths',98)
Insert into Student Values(2,'Ramesh', 'Science',99)
Insert into Student Values(2,'Ramesh', 'Social Science',90)

Insert into Student Values(3,'ram', 'Tamil',65)
Insert into Student Values(3,'Ram', 'English',68)
Insert into Student Values(3,'Ram', 'Maths',88)
Insert into Student Values(3,'Ram', 'Science',59)
Insert into Student Values(3,'Ram', 'Social Science',60)

select Student_Name,Sum(Marks_Scored) as Total
from Student
group by Student_Name
with rollup

Select
(Case When (Grouping(Student_Name)=1) Then 'G.Total' Else Student_Name End)as Student_Name,
(Case When (Grouping(SubjectName)=1) Then 'Total' Else SubjectName End)as SubjectName,
Sum(Marks_Scored) as Total
from Student group by Student_Name, SubjectName with rollup

Note :it will run sqlserver2005 and above

Serial Number using ROW_NUMBER (Transact-SQL)

Serial No is possible for your table using ROW_NUMBER


ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Check out this Example


create table myTable
(
intNo int,
fname varchar(200),
mark numeric(5,2)
)


insert into mytable values(1,'xyz',89.45)
insert into mytable values(2,'xyz1',93.45)
insert into mytable values(3,'xyz2',35.45)
insert into mytable values(4,'xyz3',88.45)
insert into mytable values(5,'xyz4',73.45)



select ROW_NUMBER()OVER(ORDER BY mark DESC) AS 'Row Number' ,intNo,
fname,mark from MyTable

output



Note:it will run in sql server 2005 and above...

Difference between MSDE and SQL Server Express 2005

SQL SERVER Express edition is a cut down version of SQL SERVER 2005 and the next evolution of MSDE(MSDE is a cut down version of SQL Server 2000).

• MSDE maximum database size is 2GB but in SQL SERVER Express has around 4GB.
  

• In terms of programming language support MSDE has only TSQL, but SQLSERVER Express has TSQL and .NET.

• SQL SERVER Express does not have connection limitation, which MSDE had and was controlled through the Workload governer.

• There was no XCOPY support for MSDE, SQL SERVER Express has it.

• DTS is not present in SQL SERVER express while MSDE has it.

• SQL SERVER Express has reporting services while MSDE does not.

• SQL SERVER Express has native XML support and MSDE does not.