Difference Between Store Procedure and user define Functions

Store Procedures

Store Procedure are compiled objects that store in databse and executed in database. Store Procedure return result more fast then query.

Difference Between Store Procedure and user define Functions

Store Procedure may or may not return a value but function must return a value.

store procedure return only integer value but function can return any type of value.

function can be use as variable but store procedure can't be used as variablein query.

Reference: howinaspnet.blogspot.com

Comments

Author: Jivani01 Apr 2013 Member Level: Gold Points : 5

*Store Procedures

(1)A stored procedure is a program (or procedure) which is physically stored within a database.

(2)Procedure can return zero or n values.

(3)Procedure can have Input, Output Parameter.

(4)Procedure allow select as well as DML statement in it

(5)Procedure can not be called from function.

(6)We can go for transaction management in procedure

(7)Procedures can not be utilized in a select statement

*User Defined Functions

(1)A user-defined function is a routine that encapsulates useful logic for use in other queries.

(2)Function returns only one value which is mandatory.

(3)UDF can have only Input Parameters.

(4)Functions allow only select statement in it.

(5)Functions can be called from Procedure

(6)In function We can't

(7)Functions can be embedded in a select statement.

Author: Phagu Mahato09 Nov 2013 Member Level: Gold Points : 1

A stored procedure could be a program (or procedure) that is physically stored at intervals and information. The advantage of a stored procedure is that once its run, in response to a user request, it's run directly by the information engine that sometimes runs on a separate information server. As such, it's direct access to the info it has to manipulate and solely has to send its results back to the user, doing away with the overhead of communication massive amounts of knowledge back and forth.

A user-defined operate could be a routine that encapsulates helpful logic to be used in alternative queries. Whereas views square measure restricted to one choose statement, user-defined functions will have multiple choose statements and supply additional powerful logic than is feasible with views.
Stored Procedures square measure pre-compile objects that square measure compiled for initial time and its compiled format is saved that executes (compiled code) whenever it's referred to as. However operate is compiled and dead when once it's referred to as. For additional concerning stored procedure and performance refer the articles differing types of stored Procedure and differing types of operate.
Basic distinction

operate should come a price however in stored Procedure it's optional (Procedure will come zero or n values).

Functions will have solely input parameters for it whereas Procedures will have input/output parameters.

Operate takes one input parameter its obligatory however stored Procedure might take o to n input parameters...

operates will be referred to as from Procedure whereas Procedures can not be referred to as from Function.

Advance distinction

Procedure permits choose similarly as DML (INSERT/UPDATE/DELETE) statement in it whereas operate permits solely choose statement in it.

Procedures can't be used in an exceedingly choose statement whereas operate will be embedded in an exceedingly choose statement.

Stored Procedures cannot be employed in the SQL statements anyplace within the WHERE/HAVING/SELECT section whereas operate will be.

Functions that come tables will be treated as another row set. This may be employed in JOINs with alternative tables.

Inline operate will be tho' of as views that take parameters and may be employed in JOINs and alternative Row set operations.

Exception will be handled by try-catch block in an exceedingly Procedure whereas try-catch block cannot be employed in a operate.

We will opt for dealing Management in Procedure whereas we won't come in operates.

Before SQL 2000, User outlined Functions (UDFs), weren't on the market. Stored Procedures were typically employed in their place. Once benefits or disadvantages of User outlined Functions square measure mentioned, the comparison is sometimes to store Procedures.

One of the benefits of User outlined Functions over stored Procedures, is that the undeniable fact that a UDF will be employed in a choose, Where, or Case statement. They can also be wont to produce joins. Additionally, User outlined Functions square measure easier to invoke than stored Procedures from within another SQL statement.
Disadvantages of User outlined Functions

User outlined Functions cannot be wont to modify base table data. The DML statements INSERT, UPDATE, and DELETE cannot be used aboard tables. Another disadvantage is that SQL functions that come non-deterministic values don't seem to be allowed to be referred to as from within User outlined Functions. GETDATE is associate degree example of a non-deterministic operate. When the operate is termed, a distinct price is came back. Therefore, GETDATE cannot be referred to as from within a UDF you produce.

Author: sudhanshu pal30 Jan 2014 Member Level: Silver Points : 4

Hello All,

Store Procedure Vs. Function in sql server
• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowsetoperations.

Author: maruthanayakam21 Feb 2014 Member Level: Silver Points : 7

Basic Difference

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
We can go for Transaction Management in Procedure whereas we can't go in Function.

Submit Article

Return to Article Index

Difference Between Store Procedure and user define Functions

Store Procedures

Difference Between Store Procedure and user define Functions

Comments

About dotnetspider.com

Quick Links

General

Services