Difference between Stored procedure and function.

Hello Experts,

Like everybody knows what is stored procedure and what is function.But my question is that is there any thing which we can only achieve by stored procedure and is there anything which we can only achieve by function?

Thanks in advance.

FIRST

In DML (Data Manipulation Language) statements: SELECT, INSERT, UPDATE, DELETE - you can't call a stored procedure like

select spName(paramname) from tableName

where, you can call a function like

select functionName(paramName) from tableName

like: select count(*) from tableName - count is a function!

SECOND

Functions should be used when you have some calculations to do. In most of the cases I use one or more functions inside a Stored Procedure. When working with T-SQL, I got to have it in habit to create a Stored Procedure and several functions that will reduce the code (like calculations) of the Stored Procedure and will be called inside this SP. The Stored Procedure will be called from the Application level (Web Application, WebSite or WinForms app) - which is fundamentially one of the reasons why SPs were invented, to be called from Application level on the most secured way.

The other things, I suppose you already know them, like - what you can achieve with both - SP & Functions.

Hope this was helpful.

So Mohit, do you have any more questions regarding this thread subject?

Thank you so much Hajan for your helping posts.

Here is what I got - 

1. Function can be used in WHERE, HAVING, and SELECT section where as Stored procedure cannot be.
2. Function cannot be used to add or update to the database where as Stored procedure can be.
3. Function can run an executable file from SQL SELECT or an action query, while Stored Procedures use EXECUTE or EXEC to run.
4. Function cannot return output parameter where as Stored procedure can be. 

Am I right Hajan

True. You are right with  all four statements!

I would like to add up something more to the 4th one:

Functions must always return a value - scalar value or table. On the other hand, Stored Procedures may return a scalar value, a table or nothing at all. As I've said, Stored Procedures can be called from Application - so the output of the Stored Procedure can be returned to the Application, while the Functions can't do that at all.

hajan said:

True. You are right with  all four statements!

Thank you so much Hajan. Keep helping:)