Click here to Skip to main content
14,449,842 members
Rate this:
Please Sign up or sign in to 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
Updated 11-Feb-20 3:40am
Comments
CPallini 11-Feb-20 3:13am
   
What about 'the documentation'?
Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
   
Comments
CPallini 11-Feb-20 3:13am
   
5.
Rate this:
Please Sign up or sign in to vote.

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100