suresh

• Nov 4th, 2006

 

functions returns value whereas procedures doesn't

boyina

 Profile Answers by boyina Questions by boyina

• Nov 7th, 2006

 

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

Ashok Kumar Prasad

• Nov 9th, 2006

 

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.

thangamani

• Dec 8th, 2006

 

according to me functions returns a value but procedure doesnot if procedure returns i need to know how it returns please give explanation.thank you

sreenivasaRao

• Dec 15th, 2006

 

Hi all

Function having return type,but Procedure does n't

Mike

• Jan 5th, 2007

 

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.

krishna pal singh

• Aug 28th, 2007

 

Function always return a values using the return statement but a procedure may or may not return values

ramumcato

 Profile Answers by ramumcato 

• May 29th, 2008

 

 Function returns some value, where as procudure doesn't return any value.