tech@faiz
  Stored procedures and User Defined Functions
 
SQL Server

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:

Download sql2008 schema here

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.

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

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.
 

Performance difference between User Defined Function and Stored Procedures

There is no difference in speed between a query run inside a function and one run inside a procedure.

Stored procedures have problems aggrgating results, they cannot be composed with other stored procedures. The onyl solution is really cumbersome as it involves catching the procedure output into a table with INSERT ... EXEC ... and then using the resulted table.

Functions have the advantage of being highly composable as a table value function can be placed anywhere a table expression is expected (FROM, JOIN, APPLY, IN etc). But functions have some very severe limitations in terms of what is permitted in a function and what is not, exactly because they can appear anywhere in a query.

So is really apple to oranges. The decision is not driven be performance, but by requirements. As a general rule anything that returns a dataset should be a view or a table valued function. Anything that manipulates data must be a procedure.
 


 
  Today, there have been 1 visitors (5 hits) on this page!  
 
Free Domain This site was last updated Monday, 23 January 2017
Copyright © 2006-2017 smfaizhaider. All rights reserved.