The most important Different in Stored Procedure & Function

Question

There are lot differents are between Stored procedure & UDF. But, what is most important different from SQL Stored procedure and User defined function. Only one different, it should be valid and why it is important different.?

 

Answer 1

We can use UDF in SP but we can not use SP in UDF

Answer 2

<strong>Function</strong> must return a value but in <strong>Stored Procedure</strong> it is optional( <strong>Procedure</strong> can return zero or n values). <strong>Functions</strong> can have only input parameters for it whereas <strong>Procedures</strong> can have input/output parameters . <strong>Functions</strong> can be called from <strong>Procedure</strong> whereas <strong>Procedures</strong> cannot be called from <strong>Function</strong>.

Answer 3

Hi Hariharan,

In store procedure you can insert,update,delete data but in function only select data.

this is a main difference between store procedure and function.

Answer 4

http://www.c-sharpcorner.com/UploadFile/2072a9/store-procedure-vs-function-in-sql-server/

http://www.tutorialplus.net/Articles/difference-between-stored-procedure-function-sql-server.htm

http://www.dotnet-tricks.com/Tutorial/sqlserver/7EDL150912-Difference-between-Stored-Procedure-and-Function-in-SQL-Server.html

Answer 5

From the tone of the question, it is not clear if you genuinely asking the question or just testing our knowledge. Anyway!

Apart from the TWO major differences mentioned already, another major difference is you cannot use output returned from sp in a join but can use returned from user defined TVF.

Furthermore you only mention UDF but don't differentiate between Scalar function and TVF.

Answer 6

Stored Procedure can return multiple result sets while Function can only return single result set which means that in Stored procedure you can have multiple select statements while in Function you cannot have that.