Character
|
Stored Procedure
|
Function
|
How to call
|
1 Function is called using
select statements.
|
1 Stored procedure can be
called independently using EXEC or EXECUTE keyword
|
Return Value
|
2 Function must return a
value(scalar, inline table or multi statement table)
|
2 Stored procedure may or may
not return a value.
|
Out Put Param
|
3 Cannot return output parameter
|
3 Can return output parameter
|
Table Variable
|
4 Can return Table variables.
|
4 Can create table but
won’t return Table Variables
|
Joins
|
5 You can join UDF
|
5 You cannot join SP
|
Server settings
|
6 Cannot be used to change server
configuration.
|
6 Can be used to change server
configuration
|
Transactions
|
7 Cannot have transaction
within function
If you can’t modify anything, there’s no
point in allowing transactions.
|
7 Can have transaction within
SP
|
Calling each other
|
8 Only extended/system stored
procedures can be called from a function.
When you create a function, SQL server will allow you to call a
procedure from the Function. However when you execute the function, it will
error out with the message “Only functions and extended stored procedures can
be executed from within a function”.
|
8 Stored procedures can call a
function or another stored procedure
|
Working with
DML , SELCT statements
|
9 Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE)
statement in it.
|
9 Function allows onlySELECT statement in it.
|
Can we use in SELECT Statement
|
10 Procedures can
not be utilized in a SELECT statement.
|
10 Function can be
embedded in a SELECT statement.
|
Usage in Filter Stmts like WHERE/HAVING/SELECT
|
11 Stored Procedures
cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
section .
|
11 Functions can
be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.
|
Exception Handling
|
12 Exception can be
handled by try-catch block in a Procedure.
|
12 try-catch block
cannot be used in a Function.
|
TableVariables
& TempTables
|
13 We can use only
table variables, it will not allow using temporary tables.
|
13 Can use both
table variables aswell astemporary table in it.
|
Execution
|
14Stored procedure can run independently. It can be executed using EXECUTE or EXEC
command
|
14 The function
cannot run independently. It has to be the part of the SQL statement
|
Data types usage
|
15 Stored procedures can use all the
data types available in sql server.
The parameters for the stored procedures
can be any data types which are available on the sql server.
|
15 function cannot use the ntext, image
and timestamp data types as return type.
The function won't allow several data
types of the sql server as a parameter.
|
|
16 Stored procedures can create table
variable and cannot return the table variable.
The table variable is one of the
performances tuning mechanism. Because it takes minimum resources and it uses
the memory location for store the data. (Recommended for minimum rows)
It can be created and do the operations.
But it cannot be the return type.
|
16 function can create, update and
delete the table variable. It can return table variable.
It can be created and can do all the DML
operations and it can be the return type. That is called the multi valued
table function.
|
|
17 Stored procedure can have the dynamic
sql statement and which can be executed using sp_executesql statement.
The stored procedure can have the
dynamic sql statement for the complex decision making operations which
generated inside the stored procedures. It can be executed using the
sp_executesql statement.
|
17 function cannot execute the
sp_executesql statement.
The function can generate the dynamic
sql statement. But it cannot get execute. It will not allow writing the
sp_executesql command to execute the dynamically created sql statement.
|
Usage of GetDate()
|
18 Stored procedure allows getdate
() or other non-deterministic functions can be allowed.
The stored procedure will allow all the sql server built-in functions like
getdate(),DB_ID(),
DB_NAME (), etc..,
|
18 function won't allow the
non-deterministic functions.
The function will not allow using
non-deterministic functions like GETDATE ()
|
Diff
b\w Stored Procedure and Function:-
1. Procedure can have both
input\output parameters
But function can have only input parameter.
2. Inside procedure we can use DML (INSERT/UPDATE/DELETE) statements.
But Inside function we can’t use DML statements.
3. We can’t utilize stored procedure in Select
Statement,
But we can use function in Select Statement.
4. We ca use Try-Catch Block
in Stored Procedure,
But Inside function we can’t use Try-Catch block.
5. Procedure can return 0 or n
values (max 1024),
But function can return only 1 value which is mandatory.
6. Procedure can’t be call from function,
But we can call function from Procedure.
7. We can go for transaction management in procedure,
But we can't go in function.
8. Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section
But we can use Function anywhere.
9.We can’t join Stored Procedure,
But We can join functions.
Differences
between Stored procedures and User defined functions
Difference
1:
Stored
procedure will be used for perform specific tasks
The stored procedure normally used to perform a speck task. The bulk of sql
statement that that will be complied and it uses the cached execution plans. It
can be return more than one result set.
Normally
functions will be used for computing value
The functions are used to
do the calculations instead of doing in the query. It can be used for many
places if we want the same operation.
Difference 2:
Stored procedures may or may not return values
The stored procedure based
on query type it will do the operation. If we write any select query then it
will return the results. If we do only update, insert or delete then it wont
return any results. However if you want to check the confirmation of the
transaction then we can return the result. It is not compulsory to return the
result set.
But
function should return value
The function must return
the value. Based on the function type it will return the results.
If we have written scalar
function then it returns single value. If we have written table valued function
then it returns multiple rows. We cannot write the function without return any
value to the calling program.
Difference
3:
Stored
procedure cannot be used in the select/where/having clause
The stored procedure
cannot be called like the following.
SELECT * FROM
Pr_RetrieveEmployees -- It will throws an error
It will throw an error.
Similarly the stored procedure cannot be part the sql query any where.
But
function can be called from select/where/having clause
The function can be called using the select query.
It can be called from the
select/where/having clause.
For instance SELECT [dbo].fn_EmployeeSalary (5) Ã it is scalar
UDF. It returns single value.
SELECT
* FROM fn_EmployeeHistory (3) Ã its will return multi value.
Difference
4:
Stored
procedure can run independently. It can be executed using EXECUTE or EXEC command
The stored procedure can
run independently. Once the stored procedure is compiled then it can be
executed. It can be executed using the sql command statement EXECUTE or EXEC.
EXECUTE proc_RetrieveEmployeeDetails
EXEC proc_RetrieveEmployeeDetails proc_RetrieveEmployeeDetails
But
function cannot run independently
The function cannot run
independently. It has to be the part of the SQL statement.
Difference
5:
Temporary
table (derived) cannot be created on function.
The temporary table cannot be created in the function. As you know if you
create a temp table then it will be stored on the tempdb database. But the temp
table won't allow us to create with inside the function
There are two ways to create the temp table.
1. Create temp table
2. Derived table
SELECT * INTO #tmpEmployee FROM
Employees
The above statement is derived table.
It cannot create on function.
But
it can be created in stored procedures
The stored procedure allows us to create the temp tables in the stored
procedure.
Difference
6:
From
sql server 2005 onwards, TRY CATCH statements can be used in the stored
procedures.
The TRY CATCH is one of the
new features in the SQL server 2005 edition. It can be used with inside the
stored procedure. As you know it handles the error in the catch block, whatever
the statements written in the try block.
But
it cannot be used in the function. But we can use raise error function.
The TRY CATCH block cannot
be used with inside the functions. But we can use the raiserror function to
throw the exception.
Difference
7:
Stored
procedure can call the user defined functions
The function can be called
from the stored procedure.
CREATE PROC Pr_RetirveCustomers AS
BEGIN SET NOCOUNT ON SET XACT_ABORT ON SELECT * FROM Customers SELECT *
FROM [dbo].fn_GetOrderedCustomers (5)
END
But
the function cannot call the stored procedures.
The function cannot call
the stored procedures like procedures. There are many types of stored
procedures in sql server.
- System Stored procedure
- User defined Stored procedure
- NET CLR stored procedure
- Extended stored procedure
Except
extended stored procedures no one can call the user defined functions.
Difference
8:
Stored
procedures can have input and output parameters.
As you know, the input and
output are the parameters which can return the results through that variable.
The output parameter can be only used to return the results through the output
variable. But the input parameter can be do the both input and output
operations.
But
the function can have only input parameters.
This won't allow us to use the output parameters. But we can use input
parameter.
Difference
9:
Stored
procedures can have select and all DML operations.
The stored procedures can
do all the DML operations like insert the new record, update the records and
delete the existing records.
But
the function can do only select operation.
The function won't allow us to do the DML operations in the database
tables like in the stored procedure. It allows us to do only the select
operation.
It will not allow to do
the DML on existing tables. But still we can do the DML operation only on the
table variable inside the user defined functions.
Difference
10:
Function
cannot have the transaction statements.
The transaction statement
cannot be used in the function. Normally we won't do any DML operations in the
function.
Stored
procedure can use transaction statements.
The transaction statement
can be used inside the stored procedures.
Difference
11:
Stored
procedures can use all the data types available in sql server.
The parameters for the
stored procedures can be any data types which are available on the sql server.
But
the function cannot use the ntext, image and timestamp data types as return
type.
The function won't allow
several data types of the sql server as a parameter.
Difference
12:
Stored
procedures can create table variable and cannot return the table variable.
The table variable is one
of the performances tuning mechanism. Because it takes minimum resources and it
uses the memory location for store the data. (Recommended for minimum rows)
It can be created and do
the operations. But it cannot be the return type.
But
the function can create, update and delete the table variable. It can return
table variable.
It can be created and can
do all the DML operations and it can be the return type. That is called the
multi valued table function.
Difference
13:
Stored
procedure can have the dynamic sql statement and which can be executed using
sp_executesql statement.
The stored procedure can
have the dynamic sql statement for the complex decision making operations which
generated inside the stored procedures. It can be executed using the
sp_executesql statement.
But
the function cannot execute the sp_executesql statement.
The function can generate
the dynamic sql statement. But it cannot get execute. It will not allow writing
the sp_executesql command to execute the dynamically created sql statement.
Difference
14:
Stored
procedure allows getdate () or other non-deterministic functions can be
allowed.
The stored procedure will allow all the
sql server built-in functions like getdate(),DB_ID(),
DB_NAME (), etc..,
But
the function won't allow the non-deterministic functions.
The function will not
allow using non-deterministic functions like GETDATE ()
Conclusion
I believe that the above
mentioned differences are valid. If you find any mistakes then please correct
that. Give your feedback comments so that I can improve my writing skills.
s tt','en-US' ) AS
FormattedDate;
