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.


  1. You can not write PRINT Statement in UDF

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

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

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

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

  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.

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

  7. What is deffered name resolution?

  8. nice difference it's good

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

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

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

  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.

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

  14. what is diffrd name resolution?pls explain

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

  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?

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

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

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

  20. Point #4 answers your question i hope.

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

  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

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

  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) i need to insert autoincrement on branchid varchar(30)....can u help me please ASAP.

    regards: maharshi,9032270738,

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

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

  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?

  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:


    SELECT localValue
    FROM dbo.localToNationalMapTable
    WHERE nationalValue = @someValue

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

  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.

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

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


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