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.
Subscribe to:
Post Comments (Atom)
You can not write PRINT Statement in UDF
ReplyDeleteYou can not write EXEC('any sql statement') inside UDF
ReplyDeleteThe core difference is that function has no side effect which means that it cannot change anything outside the function body.
ReplyDeleteFuncation can take only input parameter Where as Stored prcedure can take input and ouput parameters.
ReplyDeleteStored procedures are compiled objects where as UDF are not
ReplyDeleteGETDATE 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.
ReplyDeleteGetdate() seems to be successfully returned from a UDF in SQL 2012. You may want to check that again
DeleteWhat is deffered name resolution?
ReplyDeletePlease follow this article, to understand deffered name resolution in SQL Server.
Deletenice difference it's good
ReplyDeleteFunctions can be called from procedure whereas procedures cannot be called from function.
ReplyDeleteYes, 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.
ReplyDeleteWhy we can't use Stored procedure in UDF?
ReplyDeleteStored 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.
ReplyDeleteThe main difference is that we can use DML statements inside the stored proc but not inside functions.
ReplyDeletewhat is diffrd name resolution?pls explain
ReplyDeleteWhat is deffered name resolution in sql server, with an example
DeleteWhat parameters are passed in connection string. This question is asked 2 times by two different architect.
ReplyDeleteIn GAC if two versions are present for same dlls which one to chose by what application?
ReplyDeletehow ur code will identify which one is you should use. Give the code or command which will identify?
3 types of UDF:-
ReplyDeletea)Scalar Function
b)Inline Table Function
c)Multi-statement Table Function
we can change the server configurations through sp's but we can not change through udfs.
ReplyDeleteFunctions can't perform insert, update, delete and truncate operations but stored procedures can
ReplyDeletePoint #4 answers your question i hope.
ReplyDeleteIn stored procedures you can call function but in function you can't call stored procedures.
ReplyDeletehello sir
ReplyDeletei 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
Hi Nageswararao, please check the following video. Hope the video answers your question.
DeleteIf row exists perform an update otherwise insert
hello sir help me....i face a problem whn i am dng my project
ReplyDeletehave 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
Dynamic SQL ?
ReplyDeleteSir Please can u give an example with UDF and proc.
Udf can not modify a table values...means insert,update,delete will not work under udf..but they work normally in stored procedures
ReplyDeleteHi Venkat, we appreciate your efforts and your tutorials are excellent.
ReplyDeleteJust 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?
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.
ReplyDeleteFunctions 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)
It is mandatory for Function to return a value while it is not for stored procedure.
ReplyDeleteSelect 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.
how we can store image in sql ?
ReplyDeletestore the file path of image and for display just fetch the file path from sql table.
DeleteUDF can return tabular form but stored procedure cant not.
ReplyDelete