in which situation we will use stored procedure &userdefine function both r same?

Question

give me example using where we will stored procedure and where we will use userdefined function

Answer 1

A user-defined function is preferable when you have a function that needs to be done many times on a piece of data. A stored procedure is for a complete transaction. For example, if you need to do a string manipulation on a record, you would put the string manipulation into a UDF and then the SELECT in a stored procedure:

SELECT columnA, dbo.udfStringManip(columnB) as columnB_corrected FROM dbo.tbl

Answer 2

In addition to what the other posters have stated... one of the key differences you need to keep in kind is the alteration of data...

If you plan to alter a database table in any way (Insert, Update, Delete) you will have to use a stored procedure. User defined function cannot be used to alter table data.

You will also want to use a User defined function if you need functionality that is to be executed as part of a SQL statement. For instance, lets say you have a numeric field that actually stores a date in the format YYYMMDD you could create a function that will convert it to a displayable date of MM/DD/YYYY and place that function in your select statement. Since stored procedures cannot be called from within a Select statement, you would be required to use a User defined function.

Answer 3

A stored procedure (SQL Server) is useful if you have several programs or interfaces accessing the same information. A stored procedure can be changed without changing the program in which it is called. This is a great way to create modular coding.

a user defined function can mean many things........depending on the context in which you are referring....may or may not mean the same thing. Generally a user defined function is one which is created by the programmer and is not necessarily as modular as a stored procedure.

Answer 4

in most of the situation we use the stored procedure
but if want to reduce the complexity of the code we write the userdefine function.

if the predefined function is not suitable for our needs
we should write the functions. otherwise we will use the predefined function

Answer 5

Both are almost same but with this difference.

The user defined function returns only one value.
Whereases a stored procedure can return more then one values.

So it depends upon the situation whether you need one value or multiple.