Функция и хранимая процедура в SQL Server

разница между SP и UDF указана ниже:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

функции и хранимые процедуры служат разных целей. Хотя это не лучшая аналогия, функции можно рассматривать буквально как любую другую функцию, которую вы используете в любом языке программирования, но сохраненные процессы больше похожи на отдельные программы или пакетный скрипт.

функции нормально имеют выход и выборочно входные сигналы. Выходные данные могут быть использованы в качестве входных данных для другой функции (встроенный SQL Server, такой как DATEDIFF, LEN и т. д.) или в качестве предиката для SQL-запроса, например, SELECT a, b, dbo.MyFunction(c) FROM table или SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

хранимые procs используются для связывания SQL-запросов вместе в транзакции и взаимодействия с внешним миром. Структур, таких как ADO.NET и т. д. не может вызвать функцию напрямую, но они могут вызвать сохраненный proc напрямую.

функции имеют скрытую опасность, хотя: они могут быть использованы неправильно и вызвать довольно неприятные проблемы с производительностью: рассмотрим этот запрос:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

где MyFunction объявляется как:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

что происходит вот что функция MyFunction вызывается для каждой строки в таблице MyTable. Если MyTable имеет 1000 строк, то это еще 1000 специальных запросов к базе данных. Аналогично, если функция вызывается при указании в спецификации столбца, то функция будет вызываться для каждой строки, возвращенной SELECT.

поэтому вам нужно быть осторожными функциями записи. Если вы выбираете из таблицы в функции, вам нужно спросить себя, Может ли она быть лучше выполнена с помощью JOIN in Родительский хранимый proc или какая-либо другая конструкция SQL (например, CASE ... КОГДА... ЕЩЕ... КОНЕЦ.)

напишите пользовательскую функцию, когда вы хотите вычислить и вернуть значение для использования в других инструкциях SQL; напишите хранимую процедуру, когда вы хотите вместо этого сгруппировать возможно сложный набор инструкций SQL. Это два довольно разных ситуациях, в конце концов!

различия между хранимыми процедурами и пользовательскими функциями:

• хранимые процедуры нельзя использовать в операторах Select.
• хранимые процедуры поддерживают отложенное разрешение имен.
• хранимые процедуры обычно используются для выполнения бизнес-логики.
• хранимые процедуры могут возвращать любой тип данных.
• хранимые процедуры могут принимать большее количество входных параметров, чем задано пользователем функции. На хранении процедуры могут иметь до 21 000 входных параметров.
• хранимые процедуры могут выполнять динамический SQL.
• хранимые процедуры поддерживают обработку ошибок.
• в хранимых процедурах могут использоваться недетерминированные функции.

---

• в операторах Select можно использовать пользовательские функции.
• пользовательские функции не поддерживают отложенное разрешение имен.
• пользовательские функции обычно используются для вычисления.
• пользовательские функции должны возвращать значение.
• пользовательские функции не могут возвращать изображения.
• пользовательские функции принимают меньшее число входных параметров, чем хранимые процедуры. Пользовательские функции могут иметь до 1,023 входных параметров.
• временные таблицы не могут использоваться в пользовательских функциях.
• пользовательские функции не могут выполнять динамический SQL.
• пользовательские функции не поддерживают ошибка обращение. RAISEERROR или @@ERROR не допускаются в UDFs.
• недетерминированные функции не могут использоваться в UDFs. Например, GETDATE() не может использоваться в UDFs.

Принципиальная Разница

функция должна возвращать значение, но в хранимой процедуре это необязательно (процедура может возвращать ноль или n значений).

функции могут иметь только входные параметры для него, тогда как процедуры могут иметь параметры ввода / вывода .

функция принимает один входной параметр это обязательно, но хранимая процедура может принимать от 0 до n входных параметров..

функции могут быть вызваны из процедуры, тогда как процедуры не могут быть вызывается из функции.

Заранее Разница

процедура позволяет выбрать, а также DML (INSERT/UPDATE/DELETE) заявление в нем, тогда как функция позволяет только выбрать заявление в нем.

процедуры не могут быть использованы в инструкции SELECT, тогда как функция может быть встроена в инструкцию SELECT.

хранимые процедуры не могут использоваться в инструкциях SQL в любом месте раздела WHERE/HAVING/SELECT, тогда как функция может быть.

функции, возвращающие таблицы, можно рассматривать как другой набор строк. Это можно использовать в соединениях с другими таблицами.

встроенная функция может быть хотя как представления, которые принимают параметры и могут быть использованы в соединениях и других операциях набора строк.

исключение может быть обработано блоком try-catch в процедуре, тогда как блок try-catch не может использоваться в функции.

мы можем пойти на управление транзакциями в процедуре, тогда как мы не можем войти Функция.

источник

пользовательская функция является важным инструментом, доступным для программиста sql server. Вы можете использовать его встроенным в инструкцию SQL, например so

SELECT a, lookupValue(b), c FROM customers 

здесь lookupValue будет UDF. Такая функциональность невозможна при использовании хранимой процедуры. В то же время вы не можете делать определенные вещи внутри ОДС. Основное, что нужно помнить здесь, это то, что UDF:

• невозможно создать постоянные изменения
• не удается изменить данные

хранимая процедура может делать такие вещи.

для меня рядный использование UDF является наиболее важным использованием пользовательской функции.

Хранимых Процедуриспользуются как скрипты. Они выполняют ряд команд для вас, и вы можете запланировать их выполнение в определенное время.

функциииспользуются в качестве методов. вы передаете ему что-то, и он возвращает результат. Должен быть маленький и быстрый - это на лету.

хранимая процедура:

• это как миниатюрная программа в SQL Server.
• может быть простым, как оператор select, или сложным, как длинный скрипт, который добавляет, удаляет, обновляет и/или считывает данные из нескольких таблицы в базе данных.
• (можете реализовать циклы и курсоры, которые позволяют работать с меньшие результаты или построчные операции с данными.)
• должен быть вызван с помощью EXEC или EXECUTE заявление.
• возвращает переменные таблицы, но мы не можем использовать

чтобы решить, когда использовать то, что следующие пункты могут помочь -

1. хранимые процедуры не могут возвращать табличную переменную, где это может сделать функция as.

2. вы можете использовать хранимые процедуры для изменения параметров среды сервера, где как с помощью функций вы не можете.

ура

функции SQL Server, такие как курсоры, предназначены для использования в качестве вашего последнего оружия! У них есть проблемы с производительностью, и поэтому использование возвращающей табличное значение функции следует избегать как можно больше. Говоря о производительности, мы говорим о таблице с более чем 1 000 000 записей, размещенных на сервере на оборудовании среднего класса; в противном случае вам не нужно беспокоиться о производительности, вызванной функциями.

1. никогда не используйте функцию для возврата результирующего набора в внешний код (например ADO.Net)
2. использовать представления/хранимые как можно больше различных комбинаций. вы можете оправиться от будущих проблем с ростом производительности, используя предложения DTA (советник по настройке базы данных), которые дадут вам (например, индексированные представления и статистику) - иногда!

для получения дополнительной информации см.: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
   temporary tables                    |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                           statements.

начните с функций, которые возвращают одно значение. Самое приятное, что вы можете поместить часто используемый код в функцию и вернуть их в виде столбца в результирующем наборе.

затем, вы можете использовать функцию параметризованного списка городов. ДБО.GetCitiesIn ("NY"), который возвращает таблицу, которая может быть использована в качестве соединения.

Это способ организации кода. Знание того, когда что-то можно использовать повторно и когда это пустая трата времени-это то, что можно получить только путем проб и ошибок и опыт.

кроме того, функции являются хорошей идеей в SQL Server. Они быстрее и могут быть довольно мощными. Встроенный и прямой выбор. Будьте осторожны, чтобы не злоупотреблять.

• функция должна возвращать значение, если оно не относится к хранимой процедуре.
• операторы Select принимаются только в UDF, а операторы DML не требуются.
• хранимая процедура принимает любые операторы, а также операторы DML.
• UDF разрешает только входы, а не выходы.
• хранимая процедура позволяет использовать как входы, так и выходы.
• блоки Catch не могут использоваться в UDF, но могут использоваться в сохраненных процедура.
• никакие транзакции не разрешены в функциях в UDF, но в хранимой процедуре они разрешены.
• в UDF можно использовать только табличные переменные, а не временные таблицы.
• хранимая процедура позволяет как табличные переменные и временные таблицы.
• UDF не позволяет вызывать хранимые процедуры из функций, в то время как хранимые процедуры позволяют вызывать функции.
• UDF используется в предложении join, в то время как хранимые процедуры не могут быть используется в предложении Join.
• хранимая процедура всегда допускает возврат к нулю. UDF, напротив, имеет значения, которые должны вернуться к заданной точке.

вот практическая причина предпочесть функции хранимым процедурам. Если у вас есть хранимая процедура, которая нуждается в результатах другой хранимой процедуры, необходимо использовать инструкцию insert-exec. Это означает, что вы должны создать временную таблицу и использовать exec инструкция для вставки результатов хранимой процедуры во временную таблицу. Это грязно. Одна из проблем заключается в том, что insert-execs не могут быть вложенными.

Если вы застряли с хранимыми процедурами что вызов других хранимых процедур, вы можете столкнуться с этим. Если вложенная хранимая процедура просто возвращает набор данных, он может быть заменен табличной функцией, и вы больше не получите эту ошибку.

(это еще одна причина, по которой мы должны держать бизнес-логику из базы данных)

• функции могут использоваться в инструкции select, где as процедуры не могут.

• хранимая процедура принимает как входные, так и выходные параметры, но функции принимают только входные параметры.

• функции не могут возвращать значения типа text, ntext, image & timestamps где как процедуры могут.

• функции могут использоваться в качестве пользовательских типов данных в create table but procedures не может.

***например:-создать table <tablename>(name varchar(10),salary getsal(name))

здесь getsal-это пользовательская функция, которая возвращает тип зарплаты, когда создается таблица, для типа зарплаты не выделяется хранилище, а функция getsal также не выполняется, но когда мы извлекаем некоторые значения из этой таблицы, функция getsal get выполняется и возвращается Тип возвращается в качестве результирующего набора.

в SQL Server функции и хранимые процедуры являются двумя различными типами сущностей.

: в базе данных SQL Server функции используются для выполнения некоторых действий, и действие немедленно возвращает результат. Функции бывают двух типов: