Why use Functions in SQL if even if we have stored procedures

[SwapnilD]

Recently, in a interview, I have been asked this question.
"WHY USE FUNCTIONS IF WE HAVE STORED PROCEDURES"

I answered we cannot perform DML operations using FUNCTIONS, We can use it to retrieve data only.

But Interviewer asked me, Stored procedures could also retrieve data then why use FUNCTIONS?

I ANSWERED: Don't know, I only use PROCEDURES...


I would like to know from all u guys, what u think about this questions.

Thanks

[ck9663]

You are correct, though. You cannot use DML in function.

We also use functions for the following:
1. If we need a result set that we will use for join or UNION to another result set, table or view.
2. You can use SP to call a function, not the other way around.
3. If we want to embed the returned value in a SELECT statement, you will need a function. You can't do that in an SP. This is also true in most part of the SELECT statement like WHERE, HAVING, GROUP BY, etc...

Anything I missed?


~~ CK

[Rabbit]

The answer to the question is most likely number 3. The crux of the question is not about the overall differences between stored procedures and functions. The point of the question is for you to answer when do you have no choice but to use a function. And the only situation that I can think of when you can use a function but can't use a stored procedure is number 3. They weren't asking about the limitations of functions.

[SwapnilD]

Hi, thanks for answer, exactly what I was searching. I asked same question on another forum and what I got was differences between both (which I was already aware of).