Hi Suresh

I am not agreee with your answer because Procedure also will return the value as same as Function. tha difference between function and procedure is always function returns the value and procedure return the value only once

Hi

Difference between functions and procedure are as follows:

1. Print statement is not valid within the function because functions are meant for process or execute group of statement and return a value where as procedure are meant for executing group of statement and display output.

2. Return keyword is use to return a value from a stored function with specified data type of the value expecting to be returned from stored function.

3. One of the major advantage of stored function is that can be used in the query directly where as an stored procedure can never be used in a query as it is not returning any value.

Hi all

Function having return type but Procedure does n't

This discussion pertains to SQL Server but should apply to other database products as well.

A function returns a value directly. The value will replace the function in the location the function was called from. For example in the expression
x fn(y) + 3
the value of y will be passed to the function fn which will then calculate a result and pass the value back to the expression. Suppose fn(y) results in a value of 7. The previous expression will then be equivalent to
x 7 + 3
A function cannot change the state of the system. Any changes made in the body of the function must be undone when the function exits.
A stored procedure can change the state of the system. Stored procedures are the workhorses of a database application. Although it is not the purpose of a stored procedure to return a value a store proc can return a single status value (usually an integer) to indicate success or failure and it can return one or more values through output parameters. A major difference between the usage of a function and a stored procedure is the syntax for capturing the return values. A function can be used anyplace you would use a value as shown in the previous example. The output values from a store procedure must first be captured in variables then used in expressions:
declare @status int
declare @out1 varchar(10)
declare @out2 int
execute @status prTest @out1 output @out2 output
select Return status: @status
select Output 1: @out1
select Output 2: @out2

Getting and using a return value from a stored proc is usually a three-step process:
1. Declare one or more variables to capture the return value(s).
2. Execute the stored proc and capture the return value(s).
3. Do something with the return value(s). You could skip this step but then steps 1 and 2 would be a waste of time.