Повернення даних з збереженої процедури - SQL Server

1. Повернення даних за допомогою результуючих наборів Returning Data Using Result Sets
2. Приклади повернення даних за допомогою результуючого набору Examples of Returning Data Using a Result Set
3. Повернення даних за допомогою вихідного параметра Returning Data Using an Output Parameter
4. Приклади вихідного параметра Examples of Output Parameter
5. Використання типу даних Cursor в вихідних параметрах Using the Cursor Data Type in OUTPUT Parameters
6. Правила для вихідних параметрів курсора Rules for Cursor Output Parameters
7. Приклади вихідних параметрів курсора Examples of Cursor Output Parameters
8. Повернення даних з використанням коду повернення Returning Data Using a Return Code
9. Приклади кодів повернення Examples of Return Codes
10. Див. Також: See Also

ОБЛАСТЬ ЗАСТОСУВАННЯ: SQL Server База даних SQL Azure Сховище даних SQL Azure Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Існує три способи повернення даних з процедури в зухвалу програму: результуючі набори, параметри виведення і коди повернення. There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. Цей розділ містить відомості по всіх трьох способів. This topic provides information on the three approaches.

Повернення даних за допомогою результуючих наборів Returning Data Using Result Sets

Якщо включити інструкцію SELECT в тіло процедури, (але не SELECT ... INTO або INSERT ... SELECT), рядки, зазначені інструкцією SELECT, будуть відправлятися безпосередньо клієнтові. If you include a SELECT statement in the body of a stored procedure (but not a SELECT ... INTO or INSERT ... SELECT), the rows specified by the SELECT statement will be sent directly to the client. Для великих результуючих наборів виконання збереженої процедури не перейде до наступної інструкції, поки результуючий набір не буде повністю переданий клієнту. For large result sets the stored procedure execution will not continue to the next statement until the result set has been completely sent to the client. Для невеликих результуючих наборів результати будуть Буферізірованний для повернення клієнту, а виконання продовжиться. For small result sets the results will be spooled for return to the client and execution will continue. Якщо при виконанні процедури, що запускаються кілька таких інструкцій SELECT, клієнтові відправляється кілька результуючих наборів. If multiple such SELECT statements are run during the exeuction of the stored proceudre, multiple result sets will be sent to the client. Така поведінка також застосовується до вкладених пакетам TSQL, вкладеним збереженим процедурам і пакетам TSQL верхнього рівня. This behavior also applies to nested TSQL batches, nested stored procedures and top-level TSQL batches.

Приклади повернення даних за допомогою результуючого набору Examples of Returning Data Using a Result Set

Наведений нижче приклад показує збережену процедуру, яка повертає значення LastName і SalesYTD для всіх рядків SalesPerson, які також відображаються в поданні vEmployee. The following example shows a stored procedure that returns the LastName and SalesYTD values ​​for all SalesPerson rows that also appear in the vEmployee view.

USE AdventureWorks2012; GO IF OBJECT_ID ( 'Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD AS SET NOCOUNT ON; SELECT LastName, SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID RETURN GO

Повернення даних за допомогою вихідного параметра Returning Data Using an Output Parameter

Процедура може повертати поточне значення параметра в спричиненої програмі при завершенні роботи при вказівці ключового слова OUTPUT для параметра у визначенні процедури. If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. Щоб зберегти значення параметра в змінної, яка може бути використана в спричиненої програмі, при виконанні процедури викликається програма повинна використовувати ключове слово OUTPUT. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. Додаткові відомості про те, які типи даних можуть використовуватися в якості вихідних параметрів, див. В розділі CREATE PROCEDURE (Transact-SQL) . For more information about what data types can be used as output parameters, see CREATE PROCEDURE (Transact-SQL) .

Приклади вихідного параметра Examples of Output Parameter

Наступний приклад представляє процедуру з вхідним і вихідним параметрами. The following example shows a procedure with an input and an output parameter. Параметр @SalesPerson отримує вхідне значення, вказане викликає програмою. The @SalesPerson parameter would receive an input value specified by the calling program. Інструкція SELECT використовує значення, передане вхідному параметру для отримання вірного значення SalesYTD. The SELECT statement uses the value passed into the input parameter to obtain the correct SalesYTD value. Інструкція SELECT також привласнює це значення вихідного параметру @SalesYTD, який повертає значення викликає програмі при завершенні процедури. The SELECT statement also assigns the value to the @SalesYTD output parameter, which returns the value to the calling program when the procedure exits.

USE AdventureWorks2012; GO IF OBJECT_ID ( 'Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD @SalesPerson nvarchar (50), @SalesYTD money OUTPUT AS SET NOCOUNT ON; SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN GO

У наступному прикладі викликається процедура, яка була створена в першому прикладі і зберігає вихідне значення, повернене викликаною процедурою в змінної @SalesYTD, що є локальною в зухвалій програмі. The following example calls the procedure created in the first example and saves the output value returned from the called procedure in the @SalesYTD variable, which is local to the calling program.

- Declare the variable to receive the output value of the procedure. DECLARE @SalesYTDBySalesPerson money; - Execute the procedure specifying a last name for the input parameter - and saving the output value in the variable @SalesYTDBySalesPerson EXECUTE Sales.uspGetEmployeeSalesYTD N'Blythe ', @SalesYTD = @SalesYTDBySalesPerson OUTPUT; - Display the value returned by the procedure. PRINT 'Year-to-date sales for this employee is' + convert (varchar (10), @ SalesYTDBySalesPerson); GO

Рівні введення також можуть бути вказані для параметрів OUTPUT при виконанні процедури. Input values ​​can also be specified for OUTPUT parameters when the procedure is executed. Це дозволяє збереженій процедурі отримувати значення з спричиненої програми, змінювати його або виконувати операції з цим значенням, а потім повертати нове значення спричиненої програмі. This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. У попередньому прикладі змінної @SalesYTDBySalesPerson може бути присвоєно значення перш, ніж програма викличе процедуру Sales.uspGetEmployeeSalesYTD. In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value before the program calls the Sales.uspGetEmployeeSalesYTD procedure. Ця інструкція передає значення змінної @SalesYTDBySalesPerson вихідного параметру @SalesYTD. The execute statement would pass the @SalesYTDBySalesPerson variable value into the @SalesYTD OUTPUT parameter. Далі в тексті процедури значення можна використовувати для обчислень, які формують нове значення. Then in the procedure body, the value could be used for calculations that generate a new value. Нове значення передається назад з процедури через вихідний параметр, оновлюючи значення в змінній @SalesYTDBySalesPerson при завершенні процедури. The new value would be passed back out of the procedure through the OUTPUT parameter, updating the value in the @SalesYTDBySalesPerson variable when the procedure exits. Часто це називається «можливістю передачі по посиланню». This is often referred to as "pass-by-reference capability."

Якщо при виклику процедури вказано ключове слово OUTPUT для параметра, а параметр не визначений за допомогою OUTPUT у визначенні процедури, видається повідомлення про помилку. If you specify OUTPUT for a parameter when you call a procedure and that parameter is not defined by using OUTPUT in the procedure definition, you get an error message. Однак процедуру можна виконати з вихідними параметрами, що не вказуючи OUTPUT при виконанні процедури. However, you can execute a procedure with output parameters and not specify OUTPUT when executing the procedure. Повідомлення про помилку не буде видаватися, але не можна буде використовувати вихідне значення в спричиненої програмі. No error is returned, but you can not use the output value in the calling program.

Використання типу даних Cursor в вихідних параметрах Using the Cursor Data Type in OUTPUT Parameters

Transact-SQL Transact-SQL в процедурах тільки вихідні (OUTPUT) параметри можуть мати тип даних cursor. procedures can use the cursor data type only for OUTPUT parameters . Якщо тип даних cursor вказано для параметра, то як ключове слово VARYING, так і ключове слів OUTPUT повинні бути вказані для цього параметра у визначенні процедури. If the cursor data type is specified for a parameter , both the VARYING and OUTPUT keywords must be specified for that parameter in the procedure definition . Параметр може бути зазначений тільки як вихідний, проте якщо в оголошенні параметра вказано ключове слово VARYING, типом даних повинен бути cursor, при цьому також слід вказати ключове слово OUTPUT. A parameter can be specified as only OUTPUT but if the VARYING keyword is specified in the parameter declaration , the data type must be cursor and the OUTPUT keyword must also be specified .

Примітка

Тип даних cursor не може бути пов'язаний зі змінними додатки через інтерфейси API баз даних, таких як OLE DB, ODBC, ADO і DB-Library. The cursor data type can not be bound to application variables through the database APIs such as OLE DB , ODBC, ADO, and DB-Library. Оскільки вихідні параметри повинні бути прив'язані до того, як додаток зможе виконати збережену процедуру, збережені процедури з вихідними параметрами типу cursor не можуть бути викликані з функцій API бази даних. Because OUTPUT parameters must be bound before an application can execute a procedure , procedures with cursor OUTPUT parameters can not be called from the database APIs . Ці процедури можуть бути викликані з пакетів на мові Transact-SQL Transact-SQL, процедур або тригерів, тільки якщо вихідна змінна типу cursor присвоєна локальної змінної Transact-SQL Transact-SQL cursor мови типу. These procedures can be called from Transact-SQL Transact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact -SQL Transact-SQL local cursor variable.

Правила для вихідних параметрів курсора Rules for Cursor Output Parameters

Наступні правила застосовуються до вихідних параметрів типу cursor при виконанні процедури: The following rules pertain to cursor output parameters when the procedure is executed :

• Для курсора послідовного доступу в результуючий набір курсору будуть повернуті тільки рядки з поточної позиції курсора до кінця курсора. Поточна позиція курсору визначається при закінченні виконання процедури. Наприклад: For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the procedure execution, for example:

  • Непрокручіваемий курсор відкритий в процедурі на результуючому наборі по імені RS з 100 рядків. A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

  • Процедура вибирає перші 5 рядків результуючого набору RS. The procedure fetches the first 5 rows of result set RS.

  • Процедура повертає результат учаснику. The procedure returns to its caller.

  • Результуючий набір RS, повернутий учаснику, складається з рядків з 6 по 100 з набору RS, і курсор в учаснику позиціонується перед першим рядком RS. The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

• Для курсора послідовного доступу, якщо курсор позиціонується перед першим рядком після завершення процедури, що, весь результуючий набір буде повернутий до викликає пакету, процедурі або триггеру. For a forward-only cursor, if the cursor is positioned before the first row when the procedure exits, the entire result set is returned to the calling batch, procedure, or trigger. Після повернення позиція курсора буде встановлена ​​перед першим рядком. When returned, the cursor position is set before the first row.

• Для курсора послідовного доступу, якщо курсор позиціонується за кінцем останнього рядка після завершення процедури, що, викликає пакету, процедурі або триггеру буде повернуто порожній результуючий набір. For a forward-only cursor, if the cursor is positioned beyond the end of the last row when the procedure exits, an empty result set is returned to the calling batch, procedure, or trigger.

  Примітка

  Порожній результуючий набір відрізняється від значення NULL. An empty result set is not the same as a null value.

• Для прокручуваного курсора всі рядки в результуючому наборі будуть повернуті до викликає пакету, процедурі або триггеру після виконання процедури. For a scrollable cursor, all the rows in the result set are returned to the calling batch, procedure, or trigger when the procedure exits. При поверненні позиція курсора залишається в позиції останньої вибірки, виконаної в процедурі. When returned, the cursor position is left at the position of the last fetch executed in the procedure.

• Для будь-якого типу курсору, якщо курсор закритий, зухвалому пакету, процедурі або триггеру буде повернуто значення NULL. For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, procedure, or trigger. Це ж станеться в разі, якщо курсор присвоєно параметру, але цей курсор ніколи не відкривався. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

  Примітка

  Закрите стан має значення тільки під час повернення. The closed state matters only at return time. Наприклад, можна при виконанні процедури закрити курсор, знову відкрити його пізніше в процедурі і повернути цей результуючий набір курсора в викликає пакет, процедуру або тригер. For example, it is valid to close a cursor part of the way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, procedure, or trigger.

Приклади вихідних параметрів курсора Examples of Cursor Output Parameters

У наступному прикладі створюється процедура, яка вказує вихідний параметр @currency_cursor, використовуючи тип даних cursor. In the following example, a procedure is created that specified an output parameter , @currency_cursor using the cursor data type. Процедура потім буде викликана з пакета. The procedure is then called in a batch.

Спочатку створіть процедуру, яка оголошує і потім відкриває курсор в таблиці Currency. First, create the procedure that declares and then opens a cursor on the Currency table.

USE AdventureWorks2012; GO IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P') IS NOT NULL DROP PROCEDURE dbo.uspCurrencyCursor; GO CREATE PROCEDURE dbo.uspCurrencyCursor @CurrencyCursor CURSOR VARYING OUTPUT AS SET NOCOUNT ON; SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @CurrencyCursor; GO

Потім виконайте пакет, який оголошує локальну змінну курсора, виконує процедуру, присвоюють курсор локальної змінної, і потім вибирає рядки з курсора. Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

USE AdventureWorks2012; GO DECLARE @MyCursor CURSOR; EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT; WHILE (@@ FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO

Повернення даних з використанням коду повернення Returning Data Using a Return Code

Процедура може повертати цілочисельне значення, що називається кодом повернення, щоб вказати стан виконання процедури. A procedure can return an integer value called a return code to indicate the execution status of a procedure. Код повернення для процедури вказується за допомогою інструкції RETURN. You specify the return code for a procedure using the RETURN statement. Як і вихідні параметри, при виконанні процедури код повернення необхідно зберегти у змінній, щоб використовувати це значення в зухвалій програмі. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. Наприклад, змінна @result типу даних int використовується для зберігання коду повернення з процедури my_proc, наприклад: For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc , such as:

DECLARE @result int; EXECUTE @result = my_proc;

Коди повернення часто застосовуються в блоках управління потоком процедур для присвоєння коду повернення кожної з можливих помилок. Return codes are commonly used in control-of-flow blocks within procedures to set the return code value for each possible error situation. Щоб з'ясувати, чи відбулася під час виконання інструкції помилка, запустіть функцію @@ ERROR після інструкції Transact-SQL Transact-SQL. You can use the @@ ERROR function after a Transact-SQL Transact-SQL statement to detect whether an error occurred during the execution of the statement. До появи обробки помилок TRY / CATCH / THROW в TSQL для визначення успіху або збою збережених процедур іноді були потрібні коди повернення. Before the introduction of TRY / CATCH / THROW error handling in TSQL return codes were sometimes required to determine the success or failure of stored procedures. Збережені процедури повинні завжди видавати повідомлення при виникненні помилки (яке при необхідності створюється за допомогою THROW / RAISERROR), не покладаючись в цьому на код повернення. Stored Procedures should always indicate failure with an error (generated with THROW / RAISERROR if necessary), and not rely on a return code to indicate the failure. Крім того, слід уникати використання коду поштову картку для повернення даних програми. Also you should avoid using the return code to return application data.

Приклади кодів повернення Examples of Return Codes

У наступному прикладі показана процедура usp_GetSalesYTD з обробкою помилок, що встановлює спеціальні значення коду повернення для різних помилок. The following example shows the usp_GetSalesYTD procedure with error handling that sets special return code values ​​for various errors. У наступній таблиці показано ціле число, яке призначається процедурою кожної можливої ​​помилку, і відповідне значення кожного числа. The following table shows the integer value that is assigned by the procedure to each possible error, and the corresponding meaning for each value.

Значення кодів повернення Return code value Значення Meaning 0 0 Виконано успішно. Successful execution. 1 + 1 Необхідне значення параметра не вказано. Required parameter value is not specified. 2 + 2 Необхідне значення параметра не припустимо. Specified parameter value is not valid. 3 3 Сталася помилка при отриманні значення продажів. Error has occurred getting sales value. 4 4 Регіони Знайдено значення NULL для продажів даного менеджера. NULL sales value found for the salesperson. USE AdventureWorks2012; GO IF OBJECT_ID ( 'Sales.usp_GetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.usp_GetSalesYTD; GO CREATE PROCEDURE Sales.usp_GetSalesYTD @SalesPerson nvarchar (50) = NULL, - NULL default value @SalesYTD money = NULL OUTPUT AS - Validate the @SalesPerson parameter. IF @SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify a last name for the sales person.' RETURN (1) END ELSE BEGIN - Make sure the value is valid. IF (SELECT COUNT (*) FROM HumanResources.vEmployee WHERE LastName = @SalesPerson) = 0 RETURN (2) END - Get the sales for the specified name and - assign it to the output parameter. SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; - Check for SQL Server errors. IF @@ ERROR <> 0 BEGIN RETURN (3) END ELSE BEGIN - Check to see if the ytd_sales value is NULL. IF @SalesYTD IS NULL RETURN (4) ELSE - SUCCESS !! RETURN (0) END - Run the stored procedure without specifying an input value. EXEC Sales.usp_GetSalesYTD; GO - Run the stored procedure with an input value. DECLARE @SalesYTDForSalesPerson money, @ret_code int; - Execute the procedure specifying a last name for the input parameter - and saving the output value in the variable @SalesYTD EXECUTE Sales.usp_GetSalesYTD N'Blythe ', @SalesYTD = @SalesYTDForSalesPerson OUTPUT; PRINT N'Year-to-date sales for this employee is '+ CONVERT (varchar (10), @SalesYTDForSalesPerson);

Наступний приклад створює програму обробки кодів повернення, які повертаються процедурою usp_GetSalesYTD. The following example creates a program to handle the return codes that are returned from the usp_GetSalesYTD procedure.

- Declare the variables to receive the output value and return code - of the procedure. DECLARE @SalesYTDForSalesPerson money, @ret_code int; - Execute the procedure with a title_id value - and save the output value and return code in variables. EXECUTE @ret_code = Sales.usp_GetSalesYTD N'Blythe ', @SalesYTD = @SalesYTDForSalesPerson OUTPUT; - Check the return codes. IF @ret_code = 0 BEGIN PRINT 'Procedure executed successfully' - Display the value returned by the procedure. PRINT 'Year-to-date sales for this employee is' + CONVERT (varchar (10), @ SalesYTDForSalesPerson) END ELSE IF @ret_code = 1 PRINT 'ERROR: You must specify a last name for the sales person.' ELSE IF @ret_code = 2 PRINT 'ERROR: You must enter a valid last name for the sales person.' ELSE IF @ret_code = 3 PRINT 'ERROR: An error occurred getting sales value.' ELSE IF @ret_code = 4 PRINT 'ERROR: No sales recorded for this employee.' GO

Див. Також: See Also

DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
PRINT (Transact-SQL) PRINT (Transact-SQL)
SET @local_variable (Transact-SQL) SET @local_variable (Transact-SQL)
курсори Cursors
RETURN (Transact-SQL) RETURN (Transact-SQL)
@@ ERROR (Transact-SQL) @@ ERROR (Transact-SQL)