CLR Integration

CLR integration

Up to sql server 2000, there is concept called External Stored sub programs, that allows you to create stored sub program in c,c++ and then integrate to sql server. This feature is replaced with CLR integration in sql server 2005.

CLR Integration allows you to create stored sub programs in Dot Net languages like C#.NET, or VB.Net and then integrate to sql server. Main purpose of either external stored sub programs or CLR Integration is to allow the users to use the features like accessing files in operating system.

Using stored sub programs which are not possible with TSQL programming

CLR Integration feature is by default disable in sql server and you must enable it and for this purpose use

sp_configure stored procedure as follows

sp_configure ‘clr enabled’, reconfigure

Within visual studio.net create a project of type sqlserver database project.

Create required stored procedures, user defined functions and trigger within that project using C#.NET syntax and compile the project to generate a dll file.

While creating stored procedure , user defined function, and triggers within the sql server database project you have to follow the following rules

The method that are to be integrated as stored procedures must be specified with [Sql Procedure] attribute method to be integrated as user defined functions  must be specified with [sql function] attribute and methods to be integrated as triggers  must be specified with [sql trigger]

Methods to be integrated  into sql server must be declare with public and static

Methods to be integrated to sql server as stored procedures or triggers must have the return type void.

Methods to be integrated to sql server as triggers must  not have any parameters

Create an assembly  in sql server from ddl generated for sql server DB project using create assembly command that has the following syntax

Create assembly <assemblyname>

From <dllpath>

[with permission_set=safe| unsafe| external_access]

Create stored procedures, user defined functions and triggers with in sql server from the methods created in sql server database project by using “external name” option within create procedure , create function, and create  trigger command

Example

The following example creates one stored procedure and one user defined functions with in c#.net and integrate them with sql server

File->New Project

Add new item

And write the following code

build the solution, then .dll file is created.

EXCEPTION HANDLING

Error Handling

An exception means Runtime Error

In sql statement if any statement causes RunTime error then sql server return error msg and program execution is continued.

When any error occurs to display user friendly message and to execute error processing statement and the error must be handled.

To handle errors we need to provide a block called try-catch block

In sql server upto 2000 a system variable called @@error is used to get information about the raised error. But from sql server 2000 system variable called @@error is used to get the information about the raised error but from sql server 2005 onwards a set of functions are provided to get information about the raised error.

Syntax

Try

statement

end

catch

statements

end

example:

create procedure div(@x int, @y int)

as

begin

begin try

declare @z int

set @z=@x/@y

print @z

end try

begin catch

print 'divide by zero error'

end catch

end

Functions:

To get information about the raised error,

Sql server provides a set of functions, which are collectively called as error functions and they are as follows

Error_number()

This function is used to get the unique no of the error raised

Error_mesage()

Used to get the message related to the current user

Error_severity()

Returns the severity of the current error

Error_State()

Returns the state of the error

Error_procedure()

Returns the name of the stored sub program in which error occurs

Error_line()

Returns the line no in the stored sub program that causes error

create procedure div1(@x int, @y int)

as

begin

begin try

declare @z int

set @z=@x/@y

print 'ratio is ' +cast(@z as varchar)

end try

begin catch

print  error_number()

print error_message()

print error_severity()

print error_state()

print error_procedure()

print error_line()

end catch

end

exec div1 10,0

8134

Divide by zero error encountered.

16

1

div1

6

Every error has 4 properties to it, they are

 Msgid

Msg str

Severity

State

For example

Try the following statement

Print 100/0

This will display the following error message

Msg 8134,Level 16,state 1,

Divide by zero error encountered

In this the msgid

Of the message which is unique across server.

Message id less than 50000 is system messages

Getting list of all Error Messages

Select * from sys.messages

Adding custom message to built in error message list

Sp_addmessage

Sp_addmessage msgid,severity,msgtext

The following example adds an error message negative values are not allowed to the error message list of sql server

sp_addmessage 50001,9,'-ve values are not allowed'

Note:

Error no must be greater than 50000

The unique id of user defined errors must be greater than 50000 because error no up to 50000 is reserved for predefined error message of sql server.

sp_addmessage 50002,16,'cannot divide the no by one'

raiserror(50002,16,1)

Msg 50002, Level 16, State 1, Line 1

cannot divide the no by one

Deleting custom messages from Built in Error Messages List

You can delete user defined error messages added to predefined error messages of sql server and for this use the stored procedure

sp_dropmessage msgid

The following example deletes the error message with id 50001

sp_dropmessage 50001

sp_addmessage 50005,15,'employee already exists'

create procedure

insert_rec(@e int,@n varchar(20),@s smallmoney)

as

begin

begin try

if exists(select * from emp_temp whereeno=@e)

raiserror(50005,15,1)

if(@s<3000)

raiserror('sal>3000',15,1)

insert into emp_temp values(@e,@n,@s)

end try

begin catch

print error_message()

end catch

end

Raising Exception Manuals

There are some situations where system will not rise an exception for your requirement and you want to rise the exception Manual and for this use the function raiserror that has the following syntax

Raiserror(msgid/msgtxt,severity,state,[,arguments])

Range of severity can be from o to 25

0 to 18 system use

19 to 25 only dba  these are called fatal error, if this fatal error occurs client connection will be terminated.

19 to 25- administrator

State: the third argument state is used to identity exactly where the errors occurs in the program when the same error is raised multiple times with the same program

State values can be in the range of 0 to 255

Some error message of sql server contains format specifier like %d,%s

In, this case to specify what values to print in the place of those format specifier use arguments

sp_addmessage 50002,7,’division of %d with %d not possible

alter procedure div1(@x int, @y int)

as

begin

declare @r int

begin try

if(@x<0 or @y<0)

raiserror (50001,9,1)

else if @y=0

raiserror(50002,7,1,@x,@y)

else begin

set @r=@x/@y

print 'ratio is ' +cast(@r as varchar)

end

end try

begin catch

if ERROR_NUMBER()=50001

print '-ve values are not allowed'

else if ERROR_NUMBER()=50002

print error_message()

end catch

end

NOTE:

Now, if we want to customize the error message with formatting we can use the raiserror statement as following

Raiserror(‘cannot divide  %d with %d ‘,15,1,@x,@y)

In this case sustituing the values of variables @x at the first

We can also use the withlog option at the end of the string to write the error message into the sql server log file as following

Raiserror(‘cannot divide  %d with %d ,15,1,@x,@y) with log

After running the above procedure which will generate the error go and verify under the following location in object explorer of the management studio. i.e under the management node, sql server logs node,

Current node click on it, where we find the error message

XML IN SQL SERVER

1) MASTER

   CONTAINS THE META DATA (DATA ABOUT DATA) OF SQL SERVER  

2) MODEL (IT IS ACTS A LIKE A TEMPLATE FOR NEWLY CREATED DATABASES)
   WHATEVER YOU PLACE IN MODEL DATABASE IT IS REPLICATED TO NEWLY CREATED DATABSES

3) MSDB
   USED BY ADMINS (WHICH CONTAINS BACKUP AND RESTORE INFORAMTION
   SCHEDULED JOBS INFORMATION)

4) TEMPDB (TEMPLORARY DB) CONTAINS ALL TEMP TABLES, VARIABLES AND STORED PROCEDURES
   IF YOU RESTART SQL SERVER EVERY TIME IT CREATES A NEW INSTANCE OF TEMPDB



hOW TO FILL A TABLE FROM AN EXISTING TABLE
INSERT INTO DUMMY SELECT SNO FROM SALES

========================================================================

XML is the new data type introduced in SQL Server 2005, upto sql server 2000 varchar data type itself is used for storing XML data.

Drawbacks of this are once, xml data is stored into database, it will be treated as plain text and not as xml. To overcome this problem, in sql server 2005 xml data types is introduced. When a column is declared of type xml then it will automatically get some functions to manipulate XML data.

OPENXML provides a row set view over an XML document. Because OPENXML is a row set provider, OPENXML can be used in Transact-SQL statements in which row set providers such as a table, view, or the OPENROWSET function can appear.

COMMON XML TYPES

ATTRIBUTE SPECIFIC

          <Employees>

                   <Employee Eno="101" Ename ="Ravi" Salary="45000"/>

                   <Employee Eno="102" Ename ="Gopi" Salary="35000"/>

                   <Employee Eno="103" Ename ="Afroz" Salary="30000"/>

          </Employees>

ELEMENT SPECIFIC

          <Employees>

                   <Employee>

                             <Eno>101</Eno>

                             <Ename>Ravi</Ename>

                             <Salary>45000</Salary>

                   </Employee>

                   <Employee>

                             <Eno>102</Eno>

                             <Ename>Gopi</Ename>

                             <Salary>35000</Salary>

                   </Employee>

                   <Employee>

                             <Eno>103</Eno>

                             <Ename>Afroz</Ename>

                             <Salary>30000</Salary>

                   </Employee>

          </Employees>

DECLARE @xmlHandler int  

exec sp_xml_preparedocument @xmlHandler output, @MyData

INSERT INTO dummy

SELECT * FROM OPENXML(@xmlHandler,'Employees/Employee',2) WITH (Eno int, Ename varchar(100), Salary int)

exec sp_xml_removedocument @xmlHandler

OPENXML(): Converts xml data into rowset view.

Function Related to XML Data Types

Modify()

Is used to insert New elements into xml data, modify existing elements and delete the elements from xml data.

Examples:

create table employe1 (rowid int primary key identity(1001,1) ,empdata xml)

insert employe1(empdata) values ('<employee id="1">

<firstname>Ramesh </firstname>

<lastname>fasfs</lastname>

</employee>')

select * from employe1

Functions related to xml data types

Modify()

Modify function is used to insert new elements into xml data, modify existing elements and delete the elements from xml data.

Example:

The following example inserts another employee element next to the first employee

update employe1 set empdata.modify

('insert <employee id="2">

<firstname>sunil</firstname>

<lastname>kanth</lastname>

</employee>

after(employee)[1]') where rowid=1001

The following example inserts the job element next to last name of the first employee.

update employe1 set empdata.modify

('insert

<job>manager</job>

after(employee)[lastname][1]') where rowid=1001

TSQL PROGRAMMING

TSQL PROGRAMMING

TSQL (Transact SQL) Programming is an Procedural Language Extension to SQL which is known as PL/SQL in Oracle.

Tsql blocks are of two types

Anonymous block

Named blocks

Anonymous block

They are unnamed block of code for execution which can be written at a point where they are to be executed.They can be written on a Query Window and execute

Named block

Where the program blocks are given a name for identification. These will be stored on database which provides reusability of code.

Declare Statement

It is used to declare a variable

Syntax

Declare @varname datatype

Eg:

Declare @x int

Declare @s varchar(30), @d datetime

Set statement

It is used to assign values to a variables

Set @varname=value

Set @x=100

Note: to declare a variable In Tsql programming you have to use the keyword declare , using this keyword you can declare all the variable in a single line and you cannot break @declaration statemen  into multiple lines.

To declare variable in multiple line you must repeat the keyword declare in every line

Using the keyword set you can assign a value to only one variable and you cannot assign value to multiple variables .To do this separate with comma( ,)

Printing output:

A tsql programming to print output to the output window, you have to use the statement “Print” that has the following syntax

Print ‘output’

Print @varname

Creating block of statement

To treat set of statement with one block , enclose those statements in the keyword begin and end

Write a program to print sum of two integers

begin

declare @a int,@b int,@s int

set @a=50

set @b=60

set @s=@a+@b

print 'sum is ' +cast(@s as varchar)

end

data base programming

to do sql programming sql statement must be embed in TSQL block

all dml statement

all tcl statement

all drl command

in tsql programming , it is not possible to take inputs into variables directly from keyboard but we can take values from database table as input for variable and for this use the select statement with the following syntax

select @varname=<colname>,@varname2=<colname>,…. From <tablename> [where <condition>]

eg:

select @eno=empno, @ename=ename from emp where empno=7678

select @sal=sal from emp where empno=@eno

Conditional statement

If (condition)

Begin

Statement

End

If (condition)

Begin

Statement

End

Else

Begin

Statement

End

IF(Condition)

Begin

Statement

End   

Else if(condition)

Begin

Statement

End

Else if(condition)

Begin

Statement

End

Else

Begin

Statement

End

Case

Syntax:

Case

When <condition1> then <value1>

When <condition2> then <value2>

Else

<Else value>

End

declare @a int,@b int,@c int

begin

set @a=55

set @b=122

set @c=22

if @a>@b and @a >@c

print 'a is bigger'

else if @b>@c

print 'b is bigger'

else

print 'c is bigger'

end

write a program to print weak name of the current system data

begin

declare @week int

set @week =DATEPART(dw,getdate())

if @week=1

print 'sunday'

else if @week=2

print 'monday'

else if @week=3

print 'tuesaday'

else if @week=4

print 'wednesaday'

else if @week=5

print 'thursaday'

else if @week=6

print 'friday'

else

print 'sat'

end

      

The same program can also be written in case statement as following

begin

declare @week int

set @week =DATEPART(dw,getdate())

select case @week

when 1 then 'sunday'

when 2 then 'monday'

when 3 then 'tue'

when 4 then 'wed'

when 5 then 'thu'

when 6 then 'frid'

else 'sat'

end

end

other way of writing

begin

declare @week int

set @week =DATEPART(dw,getdate())

select case

when @week=1 then 'sunday'

when @week=2 then 'monday'

when @week=3 then 'tue'

when @week=4 then 'wed'

when @week=5 then 'thu'

when @week=6 then 'frid'

else 'sat'

end

end

while loop

while(condition)

begin

statement

end

comment in TSQL

Single line of comments

(- -)

Multiple lines of comments

(/*      dfasdfas  */

Write a program to print 1 to 10 numbers

begin

declare @n int

set @n=1

while @n<=10

begin

print @n

set @n=@n+1

end

end

declare @x int

set @x=0

while @x<10

begin

set @x=@x+1

if @x=6

break

print @x

end

declare @x int

set @x=0

while @x<10

begin

set @x=@x+1

if @x=6 continue

print @x

end

write a program to  increment salaries of employees based on job by accepting empno

president 20%

manager 25%

analyst 20%

salesman 15%

other 10%

begin

declare @eno int, @j varchar(20), @s money

set @eno=7902

select @j=job , @s=SAL FROM EMP WHERE EMPNO=@eno

if @j='PRESIDENT'

set @s=@s+@s*30/100

else if @j='manager'

set @s=@s+@s*25/100

else if @j='ANALYST'

set @s=@s+@s*20/100

else if @j='salesman'

set @s=@s+@s*15/100

else

set @s=@s+@s*10/100

update EMP set SAL=@s where EMPNO=@eno

end

NOTE:

HERE we are getting the record for only one eno, but we are not validating for all employees. To OVERCOME this we have cursors

declare

@a int,@b varchar(100)

begin

select @a=COUNT(*) from DEPT

if @a>0

begin

print 'table have record'

select @b=dname from DEPT where DEPTNO=10

print 'name of 10th department is' +@b

end

else

print 'no records'

end

Anonymous	Named
Block without names	Blocks with name
Are saved in operating system file	But the named blocks are saved in database
Every time compile and execute	Named blocks are one time compilation and everytime execution
These blocks can’t be reused	Named block are reused
Are not secured	Are secured