Stored Procedure:
A stored procedure is a pre-compiled group
of Transact-SQL statements .We can say astored procedure is a prepared SQL code
that we save so that we can reuse the code over and over again. If a repetitive T-SQL task has to be executed
within an application, then thebest way for it is to create stored procedure.
It is always recommended to create Stored
Procedure instead of writing Inline queries so that we can just call the Stored
Procedures whenever required instead of writing Inline queries again and again
each time.
You can also pass parameters to the stored
procedure, so depending on what the need is the stored procedure can act
accordingly based on the parameter values that were passed to it.
Function:
Function in Sql Server is a Transact-SQL or
common language runtime (CLR) routine that takes
parameters, performs an action, and returns
the result of that action as a value. The return value can either be a scalar
(single) value or a table.
Difference between Stored
procedure and Function
1.
Function can return only 1 value whereas Stored Procedure can return
many values(maximum 1024)
2.
Functions can have only input parameters for it whereas Stored
Procedurescanhave input/output parameters.
3.
Function takes one input parameter which is mandatory but Stored
Proceduremay take Zero to n input parameters.
4.
Functions can be used in a select statement where as Stored
Procedurescannot.
5.
Functions can be called from Stored Procedure whereas Stored
Procedurescannotbe called from Function.
6.
Stored procedures are called independently, using the EXEC command,whilefunctions
are called from within another SQL statement.
7.
Functions must always return a value (either a scalar value or a table).
Stored procedures may return a scalar value, a table value or nothing at all.
8.
Stored Procedure can be used to read and modify data but function can
only read data.
9.
Stored Procedure allows SELECT as well as DML (Data Manipulation
Language) statements like INSERT/UPDATE/DELETE in it whereas Function allows
only SELECT statement in it.
10. Procedures cannot be utilized in a SELECT
statement whereas Function can be embedded in a SELECT statement.
11.
Stored Procedures cannot be used as an inline with a select statement
whileFunctions can.
12.
Stored procedures are compiled for first time and compiled format is
saved and executes compiled code whenever it is called. But Function is
compiled and executed every time it is called.
13.
Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section whereas Function can be.
14.
Exception can be handled by try-catch block in a Procedure whereas
try-catch block cannot be used in a Function.
15.
Stored Procedure allows Transaction Management whereas Function doesn’t.
16.
Stored procedures can be used to change server configuration settings
(in terms of security-e.g. setting granular permissions of user rights) whereas
function can't be used for this
17.
The Stored Procedures can perform certain tasks in the database by using
insert, delete, update and create commands but in Function you can’t perform
use these commands.
18.
Normally the Stored procedures are used to process certain task but
theFunctions are used to compute the values i.e. we can pass some value as
input and then it perform some task on the passed value and return output.
19.
Stored Procedures can be executed using Execute or Exec command where
asFunctions can run as an executable file.
20.
Functions can be used as user defined data types in create table but
procedures cannot.