Monday 1 April 2013

A Tutorial on Stored procedures and user defined functions


A Tutorial on Stored procedures and user defined functions

This tutorial will guide us how to create stored procedures and user defined functions in SQL Server 2008.I have also tried to have a good synopsis on the differences between Stored Procedures and User defined functions with the views.
Consider the following schema for different examples quoted below:

Stored Procedure

A stored procedure is a procedure or a piece of SQL code that is physically stored in the database and it can be executed (like query) again and again for different purposes. It can be as simple as simple SQL selection query and as complex as doing multiple tasks (insertion, selection, joins, deletion) at the same time. Moreover a stored procedure is precompiled as well.
Syntax:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE PROCEDURE dbo.StoredProcedureName
  /*
     (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
     )
  */
  AS
     /* Place your Query here */
     RETURN
 
Example 1:
Create PROCEDURE dbo.GetAllEmployees
As
 
Select * from Employee
 
Execution query :
 
exec GetAllEmployees
 
Example 2:
ALTER PROCEDURE dbo.GetEmployee
@empid int
As
 
Select * from Employee where Employee.EmployeeID=@empid
 
Execution Query :
 
exec dbo.GetEmployee 1
 
Example 3:
 
Create PROCEDURE dbo.UpdateEmpNationalID
 
     (
      @empid int
      ,@newcontactid  int
      ,@oldcontactno int OUTPUT
     )
 
  AS
 
  Select @oldcontactno=[ContactID]
  FROM [dbo].[Employee] where [Employee].EmployeeID=@empid
 
  if(@oldcontactno=@newcontactid)
  begin
 
     RETURN 0
 
  end
 
else
begin
 
update Employee set ContactID=@newcontactid where Employee.EmployeeID=@empid
 
return 1
end
 
Execution Query:
Declare @oldcontactno int
Declare @returnvalue int
exec  @returnvalue=UpdateEmpNationalID 1,123,@oldcontactno output
 
Select @oldcontactno 'Old Contact'
Select @returnvalue  'Returned value'

User Defined Functions (UDFs)

User defined functions are routines that encapsulates SQL logic inside it. Like stored procedures User defined functions can also be passed input parameters but user defined functions are compiled and executed at runtime so pretty slower than stored procedures.
Syntax:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE FUNCTION dbo.Function
     (
     /*
     @parameter1 datatype = default value,
     @parameter2 datatype
     */
     )
  RETURNS /* datatype */
  AS
     BEGIN
      /* sql statement ... */
     RETURN /* value */
     END
Certain limitations for User defined functions:
i)  UDF can’t perform DML (data manipulation language) operations like Insertion, Update and Deletion on the base table.
ii) UDF can’t return non deterministic values like GETDATE () etc.
iii) Stored procedure can’t be called from inside a UDF whereas a stored procedure can call a user defined function or another stored procedure inside it.
There are three types of user defined functions:
1) Scalar Functions   (returns a single value)

Example:
?
1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION EmployeeContactID(@Empid int)
RETURNS int
AS
BEGIN
 
Declare @returnvalue int
Select @returnvalue=Employee.ContactID from Employee where Employee.EmployeeID=@Empid
        RETURN   @returnvalue
END
 
Execution:
select dbo.EmployeeContactID(1)
1) Inline Functions   (returns a table)
Example:
?
1
2
3
4
5
6
7
8
9
CREATE FUNCTION dbo.GetEmployeeFunction(@empid int)
RETURNS TABLE
AS
RETURN SELECT *
FROM employee where employee.EmployeeID=@empid
 
Execution:
 
select * from dbo.GetEmployeeFunction(1)
1) Table valued Functions  ( multiple operations, complex logic just like Stored procedures)
Example:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE FUNCTION dbo.multi_test(@empid int)
RETURNS @Result TABLE
        (
         name varchar(20)
        )
AS
BEGIN
        INSERT INTO @Result
               (name)
               SELECT [name] from employee where EmployeeID=1
 
        UPDATE @Result
        SET name = 'N'
 
        RETURN
END
 
         Exectution :
        Select * from dbo.multi_test(1)
Difference between Stored procedures and User defined functions:
i)A stored procedure is pre compiled while a User defined function is compiled and executed at runtime.
ii)A Stored procedure is more flexible than user defined function like you can write complex logic (for example exceptional handling using try catch block is possible in stored procedures which is not possible in user defined functions)
iii) A stored procedure can call another stored procedure or user defined function inside it but a user defined function can’t call stored procedure inside it.
iv)A stored procedure can return non deterministic values but a user defined function can’t return a non deterministic values like Get Date () function.
v) A user defined functions does not support DML operations like insertion, deletion and update on the base table but it is possible via stored procedure.
vi) A user defined function is easier to execute and can be used  inside selection and even for joins but stored procedure can’t be used inside selection queries and it can’t be used to join with other tables.
Some Comparisons with Views
If you think of view than a question might arise in your mind why don’t we use views instead of stored procedures or user defined functions for basic SQL selection queries. Answer is flexibility .You can’t pass parameters to views for selection of filtered queries but stored procedures and user defined functions provide you this feature.Similarly Multiple DML operations are restricted in views which are possible through stored procedures and user defined functions.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)