Books MVC ASP.NET C# SQL Server WCF Written Test HR Round Subscribe SQL Tutorial C# Programs Buy DVD

What is the difference between a User Defined Function (UDF) and a Stored Procedure (SP) in SQL Server




1. Stored Procedure
support deffered name resolution where as functions do not support deffered name resolution.



2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.


3. UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.


4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.


5. UDF should return a value where as Stored Procedure need not.


6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.



7. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.


8. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.


9. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

If you are aware of any other differences, please post them as comments, so that they will be useful for others.

24 comments:

  1. You can not write PRINT Statement in UDF

    ReplyDelete
  2. You can not write EXEC('any sql statement') inside UDF

    ReplyDelete
  3. The core difference is that function has no side effect which means that it cannot change anything outside the function body.

    ReplyDelete
  4. Funcation can take only input parameter Where as Stored prcedure can take input and ouput parameters.

    ReplyDelete
  5. Stored procedures are compiled objects where as UDF are not

    ReplyDelete
  6. GETDATE can be used as the input parameter, but could not be used inside the function itself. Other built in SQL functions that cannot be used inside a User Defined Function include: RAND, NEWID, @@CONNCECTIONS, @@TIMETICKS, and @@PACK_SENT. Any built in function that is non-deterministic cannot be used inside the function itself.

    ReplyDelete
    Replies
    1. Getdate() seems to be successfully returned from a UDF in SQL 2012. You may want to check that again

      Delete
  7. What is deffered name resolution?

    ReplyDelete
  8. nice difference it's good

    ReplyDelete
  9. Functions can be called from procedure whereas procedures cannot be called from function.

    ReplyDelete
  10. Yes, this is very good explanation and i read number of link about this,but no link is good as same.so if some one having really confusion about above question then this is proper answer.

    ReplyDelete
  11. Why we can't use Stored procedure in UDF?

    ReplyDelete
  12. Stored procedures have capability to return multiple data sets where as function can return only one data set either scalar or table. May be this can be one of the reasons that the SP cannot be called inside UDF...Pl comment.

    ReplyDelete
  13. The main difference is that we can use DML statements inside the stored proc but not inside functions.

    ReplyDelete
  14. what is diffrd name resolution?pls explain

    ReplyDelete
  15. What parameters are passed in connection string. This question is asked 2 times by two different architect.

    ReplyDelete
  16. In GAC if two versions are present for same dlls which one to chose by what application?
    how ur code will identify which one is you should use. Give the code or command which will identify?

    ReplyDelete
  17. 3 types of UDF:-
    a)Scalar Function
    b)Inline Table Function
    c)Multi-statement Table Function

    ReplyDelete
  18. we can change the server configurations through sp's but we can not change through udfs.

    ReplyDelete
  19. Functions can't perform insert, update, delete and truncate operations but stored procedures can

    ReplyDelete
  20. Point #4 answers your question i hope.

    ReplyDelete
  21. In stored procedures you can call function but in function you can't call stored procedures.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us