March 01, 2016

Difference between Stored Procedure and Function in Sql Server

Stored Procedure
  • It is a precompiled set of one or more SQL statements that is stored on Sql Server.
  • As Stored Procedures are executed on the server side and perform a set of actions, before returning the results to the client side, which allows a set of actions to be executed with minimum time and also reduce the network traffic. Hence stored procedure improve performance to execute sql statements.
  • Stored procedure can accepts input and output parameters.
  • Stored procedure can returns multiple values using output parameters.
  • Using stored procedure, we can Select,Insert,Update,Delete data in database.

Types of Stored Procedure
1. System Defined Stored Procedure
2. Extended Procedure
3. User Defined Stored Procedure
4. CLR Stored Procedure

System Defined Stored Procedure
are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database.

These procedure starts with the "sp_" prefix. Hence we don't use this prefix when naming user-defined procedures.
  • sp_rename is used to rename an database object like stored procedure,views,table etc.
  • sp_changeowner is used to change the owner of an database object.
  • sp_help provides details on any database object.
  • sp_helpdb provide the details of the databases defined in the Sql Server.
  • sp_helptext provides the text of a stored procedure reside in Sql Server
  • sp_depends provide the details of all database objects that depends on the specific database object.

Extended Procedures
provide an interface to external programs for various maintenance activities. These extended procedures starts with the "xp_" prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

User Defined Stored Procedure are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

CLR Stored Procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.

It is a database object in Sql Server and a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table.

We can’t use function to Insert, Update, Delete records in the database table(s).

Types of Functions
1. System Defined Function are defined by Sql Server for different purpose. System Defined Functions are of two types:
1.1. Scalar Function operates on a single value and returns a single value.
abs(-13.67) returns absolute number of the given number means 13.67.
rand(10) will generate random number of 10 characters.
round(17.56719,3) will round off the given number to 3 places of decimal means 17.567
upper('martin') will returns upper case of given string means 'MARTIN'
lower('MUNich') will returns lower case of given string means 'munich'
ltrim(' kempten') will remove the spaces from left hand side of 'kempten' string.
convert(int, 15.56) will convert the given float value to integer means 15.

1.2. Aggregate Function operates on a collection of values and returns a single value.
max() returns maximum value from a collection of values.
min() returns minimum value from a collection of values.
avg() returns average of all values in a collection.
count() returns no of counts from a collection of values.

2. User Defined Function are created by user in system database or in user defined database.

Difference between Stored Procedure and Function in SQL Server
1). Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.
2). Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
3). Functions can have only input parameters for it whereas Procedures can have input/output parameters .
4). Functions can be called from Procedure whereas Procedures cannot be called from Function.
5). Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
6). Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
7). Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
8). Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
9). User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.

No comments:

Post a Comment

RSSChomp Blog Directory