-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

Anji
  • 1
  • 1
  • 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. – Gordon Linoff Jul 07 '21 at 10:41
  • What are the feature available in Function but not in Stored Procedures ? – Anji Jul 07 '21 at 10:44
  • 1
    They're completely different objects, @Anji . They serve completely different things. – Thom A Jul 07 '21 at 10:45
  • 2
    Does this answer your question? [Function vs. Stored Procedure in SQL Server](https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server) – alcatraz Jul 07 '21 at 10:47

1 Answers1

-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)

Tim
  • 231
  • 1
  • 3
  • 6
  • 1
    I would, personally, say that procedures and functions aren't similar; they are in fact very different. – Thom A Jul 07 '21 at 10:46
  • *"**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. – Thom A Jul 07 '21 at 10:46
  • *"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? – Thom A Jul 07 '21 at 10:49
  • 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 – Tim Jul 07 '21 at 10:52
  • A couple of examples for you, @Tim : [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=74626b6dfad607be79fea803561eb0f0). – Thom A Jul 07 '21 at 10:55
  • Ah I realize now that this question also has an sql-server tag. My answer was for mysql – Tim Jul 07 '21 at 11:00