Advantages of User Defined Functions
Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures were often used in their place. When advantages or disadvantages of User Defined Functions are discussed, the comparison is usually to Stored Procedures.
One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement.
Disadvantages of User Defined Functions
User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables. Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.
Types of User Defined Functions
There are three different types of User Defined Functions. Each type refers to the data being returned by the function. Scalar functions return a single value. In Line Table functions return a single table variable that was created by a select statement. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.
Scalar UDFs
Our first User Defined Function will accept a date time, and return only the date portion. Scalar functions return a value. From inside Query Analyzer, enter:
CREATE FUNCTION dbo.DateOnly(@InDateTime datetime)
RETURNS varchar(10)
AS
BEGIN
DECLARE @MyOutput varchar(10)
SET @MyOutput = CONVERT(varchar(10),@InDateTime,101)
RETURN @MyOutput
END
To call our function, execute: SELECT dbo.DateOnly(GETDATE())
Notice the User Defined Function must be prefaced with the owner name, DBO in this case. In addition, 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.
The statement begins by supplying a function name and input parameter list. In this case, a date time value will be passed in. The next line defines the type of data the UDF will return. Between the BEGIN and END block is the statement code. Declaring the output variable was for clarity only. This function should be shortened to:
CREATE FUNCTION testDateOnly(@InDateTime datetime)
RETURNS varchar(10)
AS
BEGIN
RETURN CONVERT(varchar(10),@InDateTime,101)
END
Inline Table UDFs
These User Defined Functions return a table variable that was created by a single select statement. Almost like a simply constructed non-updatable view, but having the benefit of accepting input parameters.
This next function looks all the employees in the pubs database that start with a letter that is passed in as a parameter. In Query Analyzer, enter and run:
USE pubs
GO
CREATE FUNCTION dbo.LookByFName(@FirstLetter char(1))
RETURNS TABLE
AS
RETURN SELECT *
FROM employee
WHERE LEFT(fname, 1) = @FirstLetter
To use the new function, enter:
SELECT * FROM dbo.LookByFName('A')
All the rows having a first name starting with A were returned. The return is a Table Variable, not to be confused with a temporary table. Table variables are new in SQL 2000. They are a special data type whose scope is limited to the process that declared it. Table variables are stated to have performance benefits over temporary tables. None of my personal testing has found this result though.
Multi Statement UDFs
Multi Statement User Defined Functions are very similar to Stored Procedures. They both allow complex logic to take place inside the function. There are a number of restrictions unique to functions though. The Multi Statement UDF will always return a table variable–and only one table variable. There is no way to return multiple result sets. In addition, a User Defined Function cannot call a Stored Procedure from inside itself. They also cannot execute dynamic SQL. Remember also, that UDFs cannot use non-deterministic built in functions. So GETDATE and RAND cannot be used. Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions. Like other programming languages, the purpose of a User Defined Function is to create a stand-alone code module to be reused over and over by the global application.
For a Multi Statement test, we will create a modified version of the LookByFName function. This new function will accept the same input parameter. But rather than return a table from a simple select, a specific table will be created, and data in it will be manipulated prior to the return:
CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))RETURNS @Result TABLE(fname varchar(20),hire_date datetime,on_probation char(1))ASBEGININSERT INTO @Result(fname, hire_date)SELECT fname, hire_dateFROM employeeWHERE LEFT(fname, 1) = @FirstLetterUPDATE @ResultSET on_probation = 'N'UPDATE @ResultSET on_probation = 'Y'WHERE hire_date < '01/01/1991'RETURNENDTo use the new function, execute:
SELECT * FROM dbo.multi_test('A')
With the new Multi Statement Function, we can manipulate data like a Stored Procedure, but use it in statement areas like a View.
For example, only specific columns can be returned.
SELECT fname FROM dbo.multi_test('A')
The function can also be joined like a view:
SELECT e.lname, f.fname
FROM employee e INNER JOIN dbo.multi_test('A') f ON e.fname = f.fname
Conclusion
User Defined Functions offer an excellent way to work with code snippets. The main requirement is that the function be self-contained. Not being able to use non-deterministic built in functions is a problem, but if it can be worked around, UDFs will provide you with a programming plus.
Happy Programming !!
SQL 2005 allows you to use some non deterministic functions inside UDF, for example getdate()
Concise and precise. Thanks for this!
Hi,
It’s very good solution, I was looking.
Thanks.
Amresh.
Thanks, Amresh.
This is a good article, but it is pretty much an exact copy of one that was published by Don Schlichting 3 years ago on databasejournal.com – http://www.databasejournal.com/features/mssql/article.php/3348181
If you’re going to publish someone else’s work on your website, you should at least acknowledge what you’ve done, rather than trying to pass it off as your own!
David,
Thanks for your precious cooment. I will surely acknowledge the person from which I get ispiration or I had copied the article. However this is something I got from my friend as a doc file for preparation of interview and I found its cool so I have posted that.
Thanks for showing me the actual source .
One more thing, If you can provide me some more feedback to improve my blog than it will be highly appricitaed.
have a nice day.
hi chirag!
I just visited ur site to get the difference of userdfefined functions and stored procedures.It was a great help for me:)
Am also into .Net2005,C# from past 10 months in Bangalore based firm.As u too r in this field so can u give me ur mail id,so that if required i can post my queries to you through mails.
Hi Chirag!!
Do you know how to automatically update an asp.net web application after deployment of the project.So that everytime we make changes in our project it will show for an update at the client side.I found many articles regarding windows application,but i want it for web application.Below i am giving the link for that window application update.Similar thing i want to do in web application but is confused about publishing properties in web application.Please suggest or send me some links where i can find the solution.
See the below link:
http://www.codeproject.com/useritems/DDayUpdate_Part1.asp
Please reply at my mail id as soon as possible.
Thanks
Rupashi
Rupashi,
I have not done this thing prviously. Thanks for suggessting me a link for windows application. I will ask my group about web application and let you know if I found any information.
Stop palgiarizing! Who do you think you are? Your last code example has bugs in it. You can’t event copy and paste correctly. Get a life.
Man, please don’t copy others work and spoil the Indian image. Please write it in your own words at least.
Thanks
Thank you very much
[Plagiarism]
http://en.wikipedia.org/wiki/Plagiarism
very interesting, but I don’t agree with you
Idetrorce
thank u
it really helped to know the difference,i would be very thankful if you can tell me the difference between procedures and functions.
hi ,
this was really helpfull to me
im too a S/E from Sri Lanka
keep it up
regrds
romani
Hi chiragrdarji,
I’m Nguyen Van Vu, I’m Vietnamese.
Thanks very much for your excellent essay.
I’m very like database, I have some problem in SQL server, can you help me?
I have two databases on two server.
ex : A is database on first server
B is database on second server
Whether A and B database join together?
I have two databases on one server.
Ex : A and B database
A.TableA.field(X) data type is varchar
B.TableA.field(Y) data type is varchar
Select A.TableA.field(X) + B.TableA.field(Y)
then event error, i converted data but event error.
Join two tables is OK
Regrds
Vu
Hello Nguyen Van Vu,
In sybase Anywhere, there is concept called as Remote Server Connection which satisfies your requirement.
Regards,
Monika Sonia P.
add difference between user defined function and stored procedure.
when call function then the parameter must transmit full.
when call store procedure then the parameter allow empty.
ex:
CREATE FUNCTION ftest(@val01 varchar(10),@val02 varchar(10) )
CREATE PROCEDURE pTest(
@val01 varchar(10) = ’10’,
@val02 varchar(10) = NULL
)
Call:
dbo.ftest(@val01,@val02) —-> OK
dbo.ftest(@val01) —-> NOT OK
pTest(@val01,@val02) —-> OK
pTest(@val01) —-> OK
hi,
Your article is realy good with a simple language ,understand by everyone
realy nice …
thanks
Very useful for interview purpose.
This article is very fine but first it is mentioned that DML statements cannot be executed from UDFs then in Multi Statement UDF , DML commands are present.
I was not able to understand this..
Hi Chirag,
This is really a wonderful website. I have gone thru ur userdefind vs stored procedures article. It really helped me a lot in understanding the differences. Iam also a web application developer in a s/w firm working on c#+ASP.net.
Keep going on like this..
I wish u all the best..
Regards
S.Nishkal
I would like you to create the user define function Procedure for Autonumber that return value autonumber from any field and have format Abc000001. thank
Nishkal,
Thanks for your motivational comment. Be in touch.
Very nicely explained. And to the point.
Very nice explanation with example.. Actually, i was been asked this question by my interviewer, my bad, i cud not answer well.. Thanks a lot !
Hi….
i like this site. contraversy is every where. hey dudes, dont criticise others. Yes u r right, it is wrong to copy the code of others and publish it on ur site, but if u r getting the solution of ur problem in simple n easy words, then what’s the problem.
I think u should not mean from where the answer is posted, he does’nt force u to use the site , if u dont like. And also about spoling the image of Indians, why r u wondering here n there for the code on sites. R u not Indian????
chirag keep it up….
all the best…
Hi chirag
Its really very nice article,
Thank
Kanchan
Its Really a good one i ever read about the UDFs in Sql Server
Thanx
Suresh
Hi,
How can i get the output from the query using Table and Function
Like this
SELECT A.GroupNo, A.GroupName, A.BalanceType,
(select Balance from dbo.GetGroupBalance ( A.GroupNo, 1,’4-1-08′, ‘3-31-09’,A.BalanceType)) as Balance
FROM MGroup As A
Here Mgroup is Table and dbo.GetGroupBalance is Function
THANKS. ITS VERY USEFUL ARTICLE. IT HELPS ME IN MY ASSIGNMENT.
Nice contents. but what any example of stored procedures? And what is the main advantages of stored procedures?
Stored procedures are precompiled but UDF are not. So SP’s are better in terms of performance… Correct me if I am wrong.. I shd not be because I have checked and confirmed that with SQL profiler..
good examples
hey ,
how will i return a varchar from a stored procedure . can u explain with code snippets .I would really appreciate the effort.
lavan
still i can’t get the real differences between the procedure and function.otherwise make appropriet answers for this
An exact replica of article
http://www.databasejournal.com/features/mssql/article.php/3348181/User-Defined-Functions-in-Microsoft-SQL-Server-2000.htm
Hi Guys….
Aritcle is good and simple to understand… thank u so much…
But, site is imitating Pinal Dav’s site…
It will be helpfull If U provide more information about non-Deterministic functions i.e why User defined functions not support it
That was a rocking article chirag. good blog, superb actually. i admire your knowledgebase…
Hi
Good Article but We can use getdate inside a UDF
Create FUNCTION dbo.DateOnly()
RETURNS datetime
AS
BEGIN
return getdate()
end
abe gadhe copy paste toh dhang se kar liya kar.
@Niranjan and others criticizing Chirag’s article
Get a life guys…if you don’t like the article move on…do you have any blog/sites where you are posting. Atleast this guy helping other people. And if he has copied the article, may be he liked the article and wanted it to share it or keep it for his future use, if he requires it he can go and read it again instead of searching www aal over again. Although I strongly recommend acknowledging somebody else’s work.
Hi Chirag!!!
I was looking around few ques in google…I got this url…Few solutions are good here but i find also its copy from another site…Try to write unique…It will help your blog….For this type of site i should say good luck….
Thanx…..
To Mr Vivek
Your comment is good form your view….
Let me ask you a ques…. If you are looking for knowledge then will you read 1 book or many book on same topic so that you can get a proper view???
If I find same thing everywhere then why i will open google??Why i will try to open many site???
Many peoples view on same topic is good for knowledge so that we can get a good knowledge….
Hope you understand……
thanx
Hi
I have read thru your blogs. My recommendation is try to write something unique and of your own.
RJ,
Thank you for your comment. There are only 2 articles which I copied from MSDN and one blogger whose name I forgot and I have mentioned this in blog also. However I will improve. š
Chirag Darji Sr. Dotnet Consultant https://chiragrdarji.wordpress.com/
Hi,
Thanks a lot … Nice article …
Procedure can modify the state of Database but Function can not. Function can be called in query but Procedure can not.
Please see this link for more detail
http://interview-preparation-for-you.blogspot.com/2010/05/difference-between-function-and.html
fuckeers. don’t copy othres apply brain
Hi,
I have clearly understood to difference between UDF and Store Procedure from your article, Thank you vary much for your article.
you might want to correct the getdate part being used in UDF
hello sir
why we use function and stored procedur in sql server while they have same affect .
can u send me some important question on sql server on my mail
thank you
this is a good way to know about the different things which is useful for us so this is reallu a good job.
this is good explanation….for better understanding please check this link also
http://dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html
hello there,
why we need to use UDF?
This is a very common interview question asked. There are several reason why you may want to use one over the other. The following article lists all the differences between a function and a stored procedure.
Difference between a function and a stored procedure
Here is the correct URL
http://venkatsqlinterview.blogspot.com/2011/05/what-is-difference-between-user-defined.html
Hi Chirag,
This is nice information on UDFs and thanks for your information and i hope you will give more information like this…
From:
Kavitha Kiran
[…] SQL ======= Stored procedure vs User defined function https://chiragrdarji.wordpress.com/2007/04/17/difference-between-user-defined-function-and-stored-pro… […]
Good Artical
hi chirag!
I just visited ur site to get the difference of userdfefined functions and stored procedures.It was a great help for me:)
Am also into .Net2005,C# from past 10 months in pONDICHERRY based firm.As u too r in this field so can u give me ur mail id to my mail id(jayabal_e@yahoo.co.in),
so that if required i can post my queries to you through mails.
hi frnds!!
thnx for artical,n helping us!!!!!1
thnx guys!!!!thnx a lot
good article me
Hi You just tell about functions only, why u didn’t compare to store procedure. The Question is Different between storeprocedure and functions.
Good article but it is very old š SQL 2000 we re in 2011
hi it is excellent article and it is very helpful to me
thanks
Thiyagarajan T
Thanks a lot . . . now i understood the difference between them
SET @MyOutput = CONVERT(varchar(10),@InDateTime,101)
this is done in scalar udf ,why 101 is placed at last of the statement…
can u illustrate it for me????
good answer ..Need more explanation visit link -http://www.cprogrammings.com/result/Differences-Between-Stored-Procedures-and-User-Define-Function/55.html
20 main differences between Stored procedures and Functions in Sql Server
http://www.webcodeexpert.com/2013/04/difference-between-stored-procedures.html