-2

1)what are the features in Functions but not in Stored Procedure ?

2)When to use Functions instead of Stored Procedure in sql ?

3)what are the advantages of Functions over Stored Procedure ?

Hi friends i searched google i fund only difference from Functions and Stored Procedure, but did not got what are the benefits of Functions over Stored Procedure. In Functions what are features are there in Stored Procedure also have same features, then why should we use Functions ?

I am too much confusing Functions when to use Functions instead of Stored Procedure

1)When to use Functions instead of Stored Procedure in sql ?

2)what are the advantages of Functions over Stored Procedure ?

Anyone know, please help me

Improve this question
4
  • 1
    I don't even understand the basis of the question because they are so different. Functions return values and can be used in queries. Stored procedures are essentially scripts and cannot be used in SQL statements. Commented Jul 7, 2021 at 10:41
  • What are the feature available in Function but not in Stored Procedures ? Commented Jul 7, 2021 at 10:44
  • 1
    They're completely different objects, @Anji . They serve completely different things. Commented Jul 7, 2021 at 10:45
  • 2
    Does this answer your question? Function vs. Stored Procedure in SQL Server Commented Jul 7, 2021 at 10:47

1 Answer 1

Reset to default
-1

Functions and stored procedures are similar in that they are both used to store functionality that can later be run, but they are quite different too

Some important differences I've encountered:

Functions can be called in SELECT statements, procedures can not

While functions can just be called like function_name(), procedures require an explicit CALL keyword: CALL procedure_name([args]).

Functions can return one value, procedures can return multiple

Procedures don't exactly return values, but can instead get passed reference-like arguments. These can then be assigned and used as return values.

Regular arguments are marked in IN for input, arguments which you use to return values are marked as OUT for output. And you can have arguments that do both: INOUT. Example from the MySQL docs

CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
  SELECT COUNT(*) INTO cities FROM world.city
  WHERE CountryCode = country;
END

Another difference is that stored procedures can return tables, while functions can not. (unless you provide a column/columns as input)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I would, personally, say that procedures and functions aren't similar; they are in fact very different.
"Another difference is that stored procedures can return tables, while functions can not." This statement is also completely wrong. Table-value functions most certainly return "tables" (datasets); as their name implies they return a tabular result set.
"Procedures don't exactly return values, but can instead get passed reference-like arguments." If Procedures don't return values, what is the RETURN operator, or OUTPUT parameters?
Yes, you're right. I meant that they're similar in that they can both be used to store some functionality, nothing else. I have not seen anything about RETURN or OUTPUT for procedures. From a google search it seems like OUTPUT is what OUT does but in SQL Server
A couple of examples for you, @Tim : db<>fiddle.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.