Stored Procedure and Function::::Nucleous Software Java Interview Questions: Qu4

Difference between Stored Procedure and Function
-----------------------------------------------------------------------------------------------------------
                                      Answer 1

1) functions are used for computations where as procedures can be used for performing business logic

2) functions MUST return a value, procedures need not be.

3) you can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL 
query..eg: suppose, if u have a function that is updating a table.. you can't call that function in any sql query.- 
select myFunction(field) from sometable; will throw error.

4) function parameters are always IN, no OUT is possible

---------------------------------------------------------------------------------------------------------

                                          Answer 2: 

Functions and stored procedures serve separate purposes. Although it's not the best analogy, functions can be viewed literally as any other function you'd use in any programming language, but stored procs are more like individual programs or a batch script. Functions normally have an output and optionally inputs. The output can then be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc) or as a predicate to a SQL Query - e.g., SELECT a, b, dbo.MyFunction(c) FROM table or SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c). Stored procs are used to bind SQL queries together in a transaction, and interface with the outside world. Frameworks such as ADO.NET, etc. can't call a function directly, but they can call a stored proc directly. Functions do have a hidden danger though: they can be misused and cause rather nasty performance issues: consider this query: SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2) Where MyFunction is declared as: CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGERAS BEGIN    DECLARE @retval INTEGER    SELECT localValue       FROM dbo.localToNationalMapTable       WHERE nationalValue = @someValue    RETURN @retvalEND   What happens here is that the function MyFunction is called for every row in the table MyTable. If MyTable has 1000 rows, then that's another 1000 ad-hoc queries against the database. Similarly, if the function is called when specified in the column spec, then the function will be called for each row returned by the SELECT. So you do need to be careful writing functions. If you do SELECT from a table in a function, you need to ask yourself whether it can be better performed with a JOIN in the parent stored proc or some other SQL construct (such as CASE ... WHEN ... ELSE ... END).