User Define functions and stored procedure are looks simillar to each other, but there is lots of difference between them.
Here we are discussing the follwing arcticels.
1) What as User Defined Function.
2) Catagaries of User define function
3) What are stored procedure.
4) Basic difference between the User defined function and the stored procedures.
5) Create syntax of Stored Procedure
6) Create syntax of User Defined Function
1) What are 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.
2) User defined functions have 3 main categories :
1) Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
2) Inline function - can contain a single SELECT statement.
3) Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.
3) What are Stored procedure :
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.
4) Differences between Stored procedure and User defined functions :
These are the basic difference between the user define function and stored procedure
1) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2) UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3) Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations
4) We use EXEC keyword for executing the Stored procedure while no EXEC keyword requried for the execution of user defined functions, they are called by select statements.
Examples:
5) How to Create Stored Procedure.
Create Procedure [dbo].myproc(@name VARCHAR(10) , @Desg VARCHAR(10) @ID INT OUTPUT)
AS
Begin
Select @ID = ID from Employee_details where empname = @name and @empdesg = @Desg
END
--- This Procedure takes a username and its designation as an input parameter and returns its ID as a out put parameter.
6) How to Create User Define functions
Create function dbo.myfunction(@name as VARCHAR(10), @Desg VARCHAR(10))
Return INT
AS
Begin
DECLARE @ID INT
Select @ID = ID from Employee_details where empname = @name and @empdesg = @Desg
RETURNS @ID
END
|
| Author: sangeetha 13 May 2009 | Member Level: Gold Points : 2 |
Stored Procedure
================
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: Syed Shakeer Hussain 25 Dec 2009 | Member Level: Diamond Points : 0 |
Hi sangeetha.you explained very well.
|