Resources » .NET programming » Databases

Difference between stored procedure and functions in SQL Server


Posted Date: 18-Jun-2008  Last Updated:   Category: Databases    
Author: Member Level: Silver    Points: 5



1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can not be invoked from SQL Statements. Execute. SELECT
operating system can be invoked from SQL Statements. Execute. SELECT

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run


Did you like this resource? Share it with your friends and show your love!

Responses to "Difference between stored procedure and functions in SQL Server"
Author: sangeetha    12 May 2009Member Level: Gold   Points : 2
A stored procedure is a program (or procedure) which is
physically stored within a database. They are usually written in
a proprietary database language like PL/SQL for Oracle database
or PL/PgSQL for PostgreSQL. The advantage of a stored procedure
is that when it is run, in response to a user request, it is run
directly by the database engine, which usually runs on a separate
database server. As such, it has direct access to the data it
needs to manipulate and only needs to send its results back to
the user, doing away with the overhead of communicating large
amounts of data back and forth.


User-defined function
=====================

A user-defined function is a routine that encapsulates useful
logic for use in other queries. While views are limited to a
single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.





Author: Ravi kiran    08 Sep 2009Member Level: Bronze   Points : 1
1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

Kindly clarify this point with msdn, it says both are compiled and execution plans are cached.
http://msdn.microsoft.com/en-us/library/ms191007.aspx



Author: Sudarsan C.R    16 Sep 2009Member Level: Bronze   Points : 1
Functions
a) Can return only one value
b) Called from SQL Query
c) Compiled and executed during run time
Stored procedures
a) Can return more than one value
b) Can be called from SQL Query
c) Parsed and Compiled during design time itself



Author: Khaleek    31 Dec 2010Member Level: Bronze   Points : 1
Procedure can modify the state of Database but Function can not. Function can be called in query but Procedure can not.

Please see this link for more detail
http://interview-preparation-for-you.blogspot.com/2010/05/difference-between-function-and.html




Author: jatin    21 Apr 2011Member Level: Bronze   Points : 1
for better understanding of sp vs UDF please visit this link also

www.dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html



Author: sabarinathan    12 May 2011Member Level: Gold   Points : 1
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT or INOUT parameter.

2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

4.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

5. Stored procedure is precompiled execution plan where as functions are not.

6.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

7.Function can take only input aurguments, but procedure may take both input and out put parameters.



8) You can have DML(insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
eg: suppose, if u have a function that is updating a table.You can't call that function in any sql query.
- select myFunction(field) from sometable; will throw error.



9.There is possible of recurssive functionlike max(min(something)),but there is nothing like in procedure.

10.We can call a function from a procedure, but it is not possible to call a procedure from a function



11.Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations
operations on the database.Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

12.Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.



Author: ghjjfgnj    20 May 2011Member Level: Silver   Points : 1
function return value , but stored procedure not necessary to return value



we can use function in select statement but can not use stored procedure in select statement



stored procedure effect on database

function can not effects




Author: sundar    23 May 2011Member Level: Silver   Points : 1
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



Author: Manas Ranjan Das    04 Nov 2011Member Level: Bronze   Points : 4
There are many different between procedure and function,some of different given below
1)It is optional that Procedure may return values or may not but Function must return one value either a scalar value or a table.

2)functon returns table variables, while a procedure can't return a table variable although it can create a table

3)Functions can be called from procedure.Procedures can not be called from function.

If you want call procedure from fuction you have firt convert it to system define procedure then you can call procedure from fuctions

4)You have to use EXEC key work call procedure and You can't call procedure in SQL statement

but fuction call in select statement

5)procedure can take both input and output parameters but fuction take only input parameters



6)Exception handling option is there in procedure.You can use try catch block in procedure for error handing but there no such option in fuction



7)TRANSACTION management can possible in procedure but in function not possible



8)You can't use insert,update and delete statement in function but you can use this in procedure



9)procedure are pre compiled format in the database where as Functions are compiled and excuted runtime



Guest Author: cprogrammings     21 Feb 2012
good answer..need more explanation visit link --http://www.cprogrammings.com/result/Differences-Between-Stored-Procedures-and-User-Define-Function/55.html


Author: Vinod Kumar Sahu    01 Feb 2013Member Level: Silver   Points : 10
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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 ()



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Rameshkumar
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India