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.

35 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
  22. hello sir
    i faced a question in sql
    emp table with columns empid int primary key identity,ename nvarchar,email varchar
    now write a single stored procedure to insert or update a row
    if already row is there update and row is not there insert

    ReplyDelete
    Replies
    1. Hi Nageswararao, please check the following video. Hope the video answers your question.
      If row exists perform an update otherwise insert

      Delete
  23. hello sir help me....i face a problem whn i am dng my project
    have table like branchMaster , columns like branchid varchar(30) and branchname varchar(30) .....now i need to insert autoincrement on branchid varchar(30)....can u help me please ASAP.



    regards: maharshi,9032270738,maharshi357@gmail.com

    ReplyDelete
  24. Dynamic SQL ?
    Sir Please can u give an example with UDF and proc.

    ReplyDelete
  25. Udf can not modify a table values...means insert,update,delete will not work under udf..but they work normally in stored procedures

    ReplyDelete
  26. Hi Venkat, we appreciate your efforts and your tutorials are excellent.
    Just wanted to make a small correction in the above differences that you have mentioned. The correction is maximum number of input parameters allowed in SP is '2100' and not '21000'. Please let me know if I am correct or not?

    ReplyDelete
  27. 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 INTEGER
    AS
    BEGIN
    DECLARE @retval INTEGER

    SELECT localValue
    FROM dbo.localToNationalMapTable
    WHERE nationalValue = @someValue

    RETURN @retval
    END
    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)

    ReplyDelete
  28. It is mandatory for Function to return a value while it is not for stored procedure.
    Select statements only accepted in UDF while DML statements not required.
    Stored procedure accepts any statements as well as DML statements.
    UDF only allows inputs and not outputs.
    Stored procedure allows for both inputs and outputs.
    Catch blocks cannot be used in UDF but can be used in stored procedure.
    No transactions allowed in functions in UDF but in stored procedure they are allowed.
    Only table variables can be used in UDF and not temporary tables.
    Stored procedure allows for both table variables and temporary tables.
    UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions.
    UDF is used in join clause while stored procedures cannot be used in join clause.
    Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.

    ReplyDelete
  29. how we can store image in sql ?

    ReplyDelete
    Replies
    1. store the file path of image and for display just fetch the file path from sql table.

      Delete
  30. UDF can return tabular form but stored procedure cant not.

    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