Category: SQL Server Tutorial

User Defined Errors SQL Server :-

• These errors are defined by user.
• These errors are raised by using the following statement

                     Raiserror (error number/error message, error level, error state).

Example  :-  Of user defined error : Create procedure div(@x  int,@y int)

                               As

                            Begin

                           Begin try

                          Declare   @z int

                      If @y=1

              Raiserror(‘divide by one error’,15,1)

                  Set @z=@x/@y

                   Print @z

                    End try

                   Begin catch

             Print error_message ( )

                   End catch

                     End

                        ↓

                     Exec  div  10,1

                          ↓

Output → divide by one error.

Create table emp_temp :-

            (eno int,ename varchar(20),sal smallmoney)

To create a procedure to insert the record into emp_temp.

Create procedure

                Insert  _rec(@e int,@n varchar(20),@s smallmoney)

                As

               Begin

              Begin try

          If exists (select * from emp_temp where eno=@e)

              Raiserror(‘employee already exists’,15,1)

               If @s<3000

             Raiserror(‘sal>3000’,15,1)

   Insert into emp_temp values (@c,@n,@s)

         End try

     Begin catch

   Print error_message()

End catch

End

   ↓

Exec  insert

Sp_addmessage  :-

Built-in procedure used to add our errors to sys.message table.

Example  :- sp_addmessage 50005,15,’employee already exists’

                                       ↓

            Select * from sys.messages.

Program :- Create procedure

                 Insert _rec(@e int,@n varchar(20) ,@s  smallmoney)

                     As

                Begin

               Begin try

If exists (select * from emp_temp

                               Where eno=@e)

  Raiserror(50005,15,1)

 If @s<3000

   Raiserror(‘sal>3000’,15,1)

Insert into emp_temp values (@e,@m,@s)

     End try

    Begin catch

    Print error_messge ( )

       End catch

    End.

Functions in exceptions handling :

• Error_number:- This function returns error number.
• Error_message:- It returns the error message.
• Error_line:- It returns  the line number.
• Error_severity:- It returns error severity level.
• Error_state:- It returns error state level.

Every error has got mainly 4 parts :-

1. error_number
2. error_message
3. error_level
4. error_state

select * from sys.messages

↓

This table maintains the list of errors.

System defined error is up to 50,000 errors and above 50,000 is user defined errors.

Security level :-

 

• The severity level between  1 to 25
• If severity  level is between 20 to 25 those errors are called ”fatal error”, if this fatal error occurs then client connection will be terminated.

State level  :-

• It is used in user defined errors.

Example :-

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

As

Begin

Begin try

Declare @z  int

Set    @z  =1

Set @z=@x/@y

Print @z

End try

Begin catch

Print error_message()

End catch

End.

Example :- create procedure insert_rec (@e int,@n varchar, @s  int)

As

Begin

Begin try

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

End try

Begin catch

If error_number()=547

Print ‘check constraint vioated’

Else if error_number ()=2627

Print ‘primery key violated’

End catch

End

Exec   insert_rec 1,’a’,1000

Error Handling and Exception handling in SQL Server:-

• An exception means “ Run time error”
• In SQL server if any statement causes run time error then SQL Server returns “error msg” and program execution is continued.
• When any error occurs to display user friendly message and to execute error processing statements and the error must be handled.
• To handle errors we need to provide a block called “try-catch block”.

Syntax :

Create procedure <name> (parameter)

As

Begin

Begin try

Statements → it is causes error

End try

Begin catch

Statements → it is handle error

End catch

End.

if any statement in try block causes exception then control is transfer to catch block and statements in the catch block are executed.

 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

↓                                             ↓

Exec div 10,5                  Exec div 10,0

↓                                             ↓

Output → 2                    Output → divide by zero error.

Declaring parameters with default values :-

If  parameters are declared with default value at the time of calling procedure if the value is not passed to the parameter then default value assigned to that parameter.

Example  :-@x int =10

Example :-  

alter procedure

Addnum (@x  int,@y  int=20,@z int output)

As

 Begin

Set @z=@x+@y

End.

 Declare   @ k int

 Excute addnum 10,default,@k output

Print   @k

Note :-  In procedures we have 10 default values,int that case we go for named position and execute as follows:-

           Declare  @a int, @b int, @k int

                   Set @a=10

           Execute addnum  @x=@a,@z=@k output

                    Print @ k.

Create a procedure to increment particular employee salary by particular percentage :-

                Create procedure

              Updsal(@e int,@p int,@s smallmoney output)

                  As

                 Begin

                    Update emp set sal=sal+(sal*@p/100)

                                  Where empno=@e

                      Select @s=sal from emp where empno=@e

                        And.

• Write a program to I/P employee number and percentage and increment the salary by that %
• After increment if salary exceeds 5000 then cancel that update.

                         Set implicit_transaction on

                        Declare @eno int  @pen  int,   @sal smallmoney

                       Set @eno=7566

                       Set @pen=70

              Execute updsal @eno,@pen,@sal output.

                If @sal>5000

                       Rollback

                    Else

                 Commit.

Procedures in SQL Server :-

• A procedure is a pre-compiled named block stored in database that performs a task & may (or) mayn’t return a value.
• Procedures are stored in database, so it is called “stored procedures”
• Procedures are created to perform DML operations over database [transactions].

Syntax for creating procedure :—

Create/alter procedures <name> → procedure declaration

(Parameters)

As

Begin

            Statements → procedure body

End.

Parameters are two types :–

• Formal
• Actual

Example :- 

                k= add(a,b)

                Int add(int x,int y)

                 {

                    Return x+y;

                   }

parameters which are declared in procedures declaration that called “formal parameter”

Parameters  which are passed at the time of calling procedure are called “actual parameter”.

Formal & actual parameters are two types  :-

1. Input
2. Output

Input  :-

• Input parameters are always receive value in a program and it is a default and it is read only.

Output :-

• output parameters are always sends value to  main program.
• And it is write only

Example for input Parameter :-

Create a procedure to add two numbers  :-

      Create procedures addnum(@x int,@y int)

                   As

                  Begin

                  Declare    @z  int

                  Set @ z=@x+@y

                    Print  @z

      End

• Once procedure is created the procedures can be executed from [SSMS] SQL Server management studio (or) .net application (or) java application etc.
• In SSMS, procedures can be executed in two ways:

               (I) Positional notation

               (II) Named notation

(I) Positional notation in SQL  :-

• In positional rotation parameters are mapped through their position.

Example:  (how to execute the above procedure) in positional notation.              

Execute addnum  50,30

(II) Named notation in SQL  :-

• In named notation the parameter are mapped through their names.

Example  :-   {how to execute the above procedure in named notation}

                   Declare @a int,@b int

                  Set   @ a=40

                   Set @ b=60

          Execute addnum   @y=@b,@x=a(or)

                                         @x=@a,@y=@b

Output parameter example  :-

   Alter procedure

  Addnum(@x int,@y int,@z  int output)

     As

    Begin

  Set @z=@x+@y

     End

How to execute  :-

               Declare @a int,@b int,@c int

                 Set @a=30

                 Set @b=40

          Execute addnum @a,@b,@c output

              Print @c.

Sub-Programs in SQL Server  :- 

Named blocks:

1. Procedures
2. Functions
3. Db triggers

procedures & functions are also called “sub-programs”

subprograms means called in main program.

Difference between Anonymous and named:

Anonymous	named
Block without name anononymous blocks are saved in “operating system file” These blocks are not secured.  These blocks can’t be re-used.  Every time compile & executed.	blocks with name. but the named blocks are saved in “database” but named blocks are secured. but named blocks can be re-used. Named blocks are one time compilation And every time execution

Main advantages of creating procedures and functions :-

(I) Reusability

(II) Pre-compiling

Local and Global Cursors  :-

• If cursor is local then the cursor can access with in the program only.
• If cursor is global then the cursor can be declared in one progress can be used in another program.

Global is default

Program (I) :-

             Declare c1 cursor global

                      For select ename,sal,comm from emp.

               Declare  @ename varchar(20)

                Declare @sal smallmoney  @comm smallmoney.

                Declare @totsal smallmoney.

                            Open c1

                           Fetch next from c1 into @ename,@sal,@comm

                            While (@@fetch_status=0)

                                 Begin

                              Set @totsal=@sal+isnull(@comm,0)

                              Print  @ename+’earns’+cast(@total as varchar)

                         Fetch next from c1 into @ename,@sal,@comm

                          End.

                             Close c1.

Note  :-

Program(II)  :-  Calculating   annual salary using cursor c1:

                                        Declare  @ename varchar(20)

                                         Declare   @sal smallmoney,@comm smallmoney

                                         Declare @totsal smallmoney

                                              Opn c1

                                      Fetch next from c1 into @ename,@sal,@comm

                                       While (@@fetch_status=0)

                                         Begin

                                          Set @totsal=(@sal+isnull(@comm,0))*12

                                          Print  @ename+’earns’+cast(@totsal as varchar)

                                        Fetch next from c1 into @ename,@sal,@comm

                                           End

                                         Deallocate c1.

                            Order_details                                     items

      Ordno         icode             qty                     icode         descrpt        price

          1000           1                   10                        1                                  100

         1000            2                   30                         2                                  150

          1000           3                   15                         3                                  50

          1001           1                 20

To display like :-   

              Item code             dept                 rate            qty             value

                   1                         —                   100            10               1000

                   2                         —                    150            20              3000

                   3                         —                    50              15               750

Total bill=4750

Program :-

                        Declare   c1   cursor

                            For select    icode,descno,rate,qty

                               From   order-details,items

                      Where   order_details icode=items.icode

                                     And      andno=1000.

                      Declare @icode int,@descrip varchar(20)

                      Declare   @rate   int,@qty int

                         Declare @value int

                        Declare   @tbil int.

                       Set @tbil=0

                    Open c1

    Fetch  next from c1 into @icode,@descrip,@rate,@qty

                    While (@@fetch_status=0)

                   Begin

                   Set @value=@qty*@rate

       Set   @tbill=@tbill+@value

          Print  cast (@icode as varchar)+’ ’+@descrip+

                 ‘ ‘+cast(@rate as varchar) +’ ‘+cast+@qty

                          As varchar)+cast(@value as varchar)

                   Fetch next from c1 into @icode,@descrip @rate@qty.

                    End

                       Print ‘total bill :-‘+cast(@tbill as varchar)

                        Close c1

                     Deallocate c1.

Static and Dynamic Cursor in SQL Server  :-

Static Cursor in SQL Server with Example :

• If the cursor is declared with static any changes make to the base table changes are not reflected to result-set.

Example  :-

•                  Emp                             Resultset
•      Name          sal                       name          sal
•          A             5000                    A              5000
•                              ↓                                             ↓
•                           Update                              5000

Dynamic Cursor in SQL Server with Example   :-

• If the cursor is declared with dynamic so any changes make to the base table the changes are automatically reflected to result set.

Example  :-       

                   emp                                   resultset

            Name      sal                          name     sal

              A          5000                         A        5000

                            ↓ update                             ↓

                           6000                            6000

Example : program  :-

                   Declare c1 cursor static [(or) dynamic]

                        For select sal  from emp  where empno=7788

                   Declare  @sal   smallmoney

                     Open c1

        Update emp  set sal =5000              (=6000 if dynamic)

                         Where empno=7788

   Fetch next from c1 into @sal

    Printt @sal

      Close c1

     Deallocate c1.

Output :-  4000 (if static)

                   6000(if dynamic)

Scroll Cursor in SQL Server :-

Example  :-

Declare c1 cursor scroll for select  statement

By default cursors supports forward only scrolling and supports fetch next statement.

If cursor is declared with scroll option it can fetch both forward & backward scrolling, and also supports all fetch statement like fetch next prior, first,last, absolute n, relative n.

Absolute n:- Means starting from first record fetch the ‘n’th record.

Example  :-

1

2

3                 absolute 4

4                  Ans: 5^th record.

5

Relative n :- means   fetches the ‘n’ th record starting from current record.

Example  :- 

           Declare c1 cursor scroll

                For select ename from emp

           Declare @ename varchar(20)

             Open c1

          Fetch first from c1 into @ename

          Print @ename

           Fetch  next from c1 into @ename

            Print @ename.

         Fetch absolute 5 from c1 into @ename

           Print @ename

         Fetch relative 5 from c1 into @ename

           Print @ename

            Fetch last from c1 into @ename

               Print @ename

           Fetch prior from c1 into @ename

           Print @ename

             Close c1

             Deallocate c1.

Write a program to display every 4^th record in emp table?

      Declare c1 cursor scroll

             For select ename from emp

      Declare @ename varchar (20)

         Open c1

     Fetch first from c1 into @ename

        While (@@ fetch_status==0)

          Begin

                Print @ename

               Fetch relative 3 from c1 into @ename

            End

          Close c1

         Deallocate c1.

@@fetch_status in SQL Server :-

• This is one system variable which returns status of the fetch statement  i:e fetch is successful or not.
• If fetch is successful returns 0, otherwise non-zero value.

Write a program to display all employee names & salaries :-

Declare c1 cursor for

        Select ename,sal from emp

Declare @ename varchar (20)

Declare  @sal smallmoney

 Open c1

Fetch next from c1 into @ename,@sal

       While (@@fetch_status=0)

              Begin

              Print  @ename +’earns’+cast(@sal as varchar)

    Fetch next from c1 into @ename,@sal

             End

         Close c1

     Deallocate   c1. 

            Student                                                       Result

Sno     sname     s1   s2   s3                    sno    stot       savg     snes

 1            X         50   60    70

 2            Y         60   75     82

• Create table student (sno int,sname varchar(20),  S1 int,s2  int  ,s3 int)

• Create table result (sno int,stot int,savg int,snes  char(4)).
• Insert into student values(1 ,’A”,50,60,70)
• Insert into student values (2,’B’,60,52,75)
• Select * from student.

Program  :

                       Declare c1 cursor

                      For select sno,s1,s2,s3 from student.

                     Declare @sno int,@s1 int ,@s2 int,@s3 int

                      Declare @ stot int,@savg int

                       Declare @snes  char(4).

                 Open c1

                     Fetch next from c1 into @sno,@s1,@s2,@s3

                         While (@@ fetch_status==0)

                                 Begin

                       Set @stot=@s1+@s2+@s3

                        Set @savg  =ceiling((@stot)/3)

                           If @s1>35  and @s2>35 and @s3>35

                            Set @snes=’pass’

                              Else

                             Set @snes =’fail’

                    Insert into result values (@sno,@stot,@savg,@snes)

                     Fetch next from c1 into @sno,@s1,@s2,@s3

                       End

                      Close c1

                       Deallocate c1.

Output :-                   sno         stot     savg     snes                 [select * from result]

                                          1             A       55          pass

                                         2              B        60         pass