stored procedure and functions retruns value ? then what exact difference ?

what you describe is basically the difference.

the ramifications of it are in the use.


You can't have output parameters in a function that is used in a sql statement

Functions return a value.  Procedures have out parameters.

also stylistically,  sometimes a function vs procedure carries meaning and/or intent.

Functions "tend" to be data lookups/conversions.    You give me X,  I'll return Y.
You can, of course, write a function to do just about anything though.

Procedures "tend" to be data processors.  
You give me X,  I'll go do a bunch of stuff for you.  I may or may not return anything from those efforts.
Again, though, you can write a procedure to do lookups/conversions too.

1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.

3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can't use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure

3 - not true.    functions can call procedures

4 - the "return" value of a function is single value/object, but the function "could" have OUT parameters too, but then it wouldn't be callable from SQL

5 - I'm not entirely sure what you mean by this,  both SQL Server and Oracle can use joins to determine values returned, but I might be misunderstanding what you intended on this point

6 - Not true for Oracle

7 - "print" doesn't apply to Oracle, in oracle dbms_output can be called from either

The above points are all based on SQL server.

3. I tried sometime back but wasn't able to call it inside the function. On surfing, I found some blogs where it says we can't call a procedure within a function.

5. We can cross apply the output from the function, i.e. can use the function in joins.

Exact differences are:

1) you cannot use DDL and DML (Create,Alter, Drop, Truncate,Insert,Update, Delete) statements in functions

2) You can use functions (System and User defined functions) in Queries (Select) statements.
System Functions : MAX,MIN,AVG ect..
Ex:                           SELECT MAX(SAL) FROM EMPLOYEE

User Defined Functions:  GetMaxIncentiveEarnedDate()

SELECT EMP_ID,EMP_NAME,GetMaxIncentiveEarnedDate(EMP_ID) as MaxIncentiveEarnedDate
FROM EMPLOYEE

3 - sorry, I was referring to Oracle but didn't specify

>>you cannot use DDL and DML (Create,Alter, Drop, Truncate,Insert,Update, Delete) statements in functions

In Oracle you can with a 'feature'.  Not that you would want to make a habit of it.  Just saying it is possible.

drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','1');
commit;

create or replace function myfunc return varchar2
is
	pragma autonomous_transaction;
begin
	execute immediate 'drop table tab1 purge';
	return 'Hello';
end;
/

show errors

Select allOpen in new window

It seems a confusion is arising across technology lines.  SQL Server functions are not comparable to Oracle.  Question: why are we talking about Oracle implementation a question asked in SQL Server section ?  

>>why are we talking about Oracle implementation a question asked in SQL Server section ?  

Cross posted in two zones...

Zones:
MS SQL Server, Oracle Database

slightwv:

Your answer is irrelevant to this question. This question is asked for SQL Server not for Oracle.

The main difference is you can use Functions in Queries (Select statements) in all RDBMS systems.

>>This question is asked for SQL Server not for Oracle.

How do you know?  This was cross posted in BOTH SQL Server and Oracle zones.

Until the asker comes back to clarify, we have to assume this is a generic question covering both products.

slightwv:

Here the sql server will retruns the value and stored procedure will returns the value , ?

Above is the question and author asked for SQL server.

>>> author asked for SQL server.


read the question again, including the zones it was posted in

this is a question for 2 databases because it was posted to 2 zones.
You might have found the question by coming through sql server zones,  I found it by coming though Oracle.

The asker, posted to both; so, it is appropriate to give answers that cover both platforms

<<Cross posted in two zones...>>
Guess that is the initial source of the confusion.  However, the OP specifically mentionned he was refering to SQL Server in his question which pretty makes irrelevant most Oracle oriented answers (no disrespect meant to the Oracle crowd)..

<<Until the asker comes back to clarify, we have to assume this is a generic question covering both products.>>
I believe the questionner was clear enough when he mentionned:
"Here the sql server will retruns the value and stored procedure will returns the value."

But perhaps this is not clear enough

No big deal in fact.

To the asker

As you can see there are significant differences between the characteristics of SQL and Oracle functions.  It would be helpful if you could specifiy which one is most relevant for you.

interesting,  

Had the question been crossposted to sql server 2005 and sql server 2008 and just happened to mention 2008 in the text
You would assume the 2005 zone should be ignored?

What if it had been crossposted to 3 zones and the references to them in the text were disproportionate?


I'll acknowledge that the asker might have mis-zonzed the question, but it seems odd to intentionally ignore a piece of information that the asker has provided simply because another piece was mentioned one additional time.

Hi

Here I required the difference between Stored Procedure and Functions in both databases . Please clarify me on both zones.

                                    Many thanks.

thanks a lot , i got good Clarification

I appreciate the points, but...

http:#37107026 doesn't really answer the question, it's just a comment about the previous solutions that did answer it.
a split would probably be in order