Why we use user defined function in SQL server?

Question

1.00/5 (1 vote)

See more:

What is main use of function in sql server. if we use select statement in Stored Procedure than why we use function? what is the main purpose of function

What I have tried:

I want to know about that only

Posted 10-Feb-20 19:26pm

Gauravg9598

Updated 11-Feb-20 2:40am

Add a Solution

Comments

CPallini 11-Feb-20 3:13am

What about 'the documentation'?

2 solutions

Solution 2

Aside from the fine answer already submitted, I will give you an answer from the other side of 'why?'.

A function can supply a single concise and easily understood (if well named) method to do a certain job with the SQL data - to in some manner - transform it - and ultimately to make the job you are doing easier.

An example of one I created converts numeric values to two-decimal right-justified comma-delimited (output with HTML styles for justification). This result makes it easy to represent dollar values in table columns neatly aligned for easy comparison by eye. The SQL is now easy to read and the client side has a lot of work (and clutter) removed.

Another example - the built-in UPPER() converts input string to upper-case. You can do it yourself and imagine the mess in your SQL! Or, of course, do the same thing on the client side with javaScript or php. Consider: Why would you ever write a function in any language? That's your starting point.

They can do much more complex operations than that - but the preceding is easy to visualize.

Posted 11-Feb-20 2:41am

W∴ Balboos, GHB

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-02-10T20:17:00

Functions and stored procedures are not the same thing, or even close.
The really important difference is that functions cannot alter table data at all: SELECT is OK, but INSERT, UPDATE, and DELETE operations are forbidden.
The next most important is that you can use a Function in an SQL statement (even if that statement is inside a Stored Procedure) but you cannot use an SP inside a Function.

There are other differences, which are summarised here: Difference between Stored Procedure and Function in SQL Server[^]