SQL Server/T-SQL Tutorial/Transact SQL/Return

Материал из SQL эксперт
Перейти к: навигация, поиск

Assign returned value from a function to a declared variable

   <source lang="sql">

3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL) 18> GO 1> create PROC spInsertDateValidatedOrder 2> @CustomerID nvarchar(5), 3> @EmployeeID int, 4> @OrderDate datetime = NULL, 5> @RequiredDate datetime = NULL, 6> @ShippedDate datetime = NULL, 7> @ShipVia int, 8> @Freight money, 9> @ShipName nvarchar(40) = NULL, 10> @ShipAddress nvarchar(60) = NULL, 11> @ShipCity nvarchar(15) = NULL, 12> @ShipRegion nvarchar(15) = NULL, 13> @ShipPostalCode nvarchar(10) = NULL, 14> @ShipCountry nvarchar(15) = NULL, 15> @OrderID int OUTPUT 16> AS 17> DECLARE @Error int 18> DECLARE @InsertedOrderDate smalldatetime 19> DECLARE @INVALIDDATE int 20> SELECT @INVALIDDATE = -1000 21> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 OR @OrderDate IS NULL 22> BEGIN 23> PRINT "Invalid Order Date" 24> PRINT "Supplied Order Date was greater than 7 days old " 25> PRINT "or was NULL. Correct the date and resubmit." 26> RETURN @INVALIDDATE 27> END 28> SELECT @InsertedOrderDate = 29> CONVERT(datetime,(CONVERT(varchar,@OrderDate,112))) 30> PRINT "The Time of Day in Order Date was truncated" 31> INSERT INTO Orders VALUES(@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate,@ShippedDate, 32> @ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry 33> ) 34> SELECT @Error = @@ERROR 35> IF @Error ! = 0 36> BEGIN 37> IF @Error = 547 38> BEGIN 39> PRINT "Supplied data violates data integrity rules" 40> PRINT "Check that the supplied customer number exists" 41> PRINT "in the system and try again" 42> END 43> ELSE 44> BEGIN 45> PRINT "An unknown error occurred. Contact your System Administrator" 46> PRINT "The error was number " + CONVERT(varchar, @Error) 47> END 48> RETURN @Error 49> END 50> SELECT @OrderID = @@IDENTITY 51> 52> RETURN 53> GO 1> 2> 3> DECLARE @MyIdent int 4> DECLARE @MyDate smalldatetime 5> DECLARE @Return int 6> 7> SELECT @MyDate = "1/1/1999" 8> EXEC @Return = spInsertDateValidatedOrder 9> @CustomerID = "ALFKI", 10> @EmployeeID = 5, 11> @OrderDate = @MyDate, 12> @ShipVia = 3, 13> @Freight = 5.00, 14> @OrderID = @MyIdent OUTPUT 15> 16> IF @Return = 0 17> SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName 18> FROM Orders 19> WHERE OrderID = @MyIdent 20> ELSE 21> PRINT "Value Returned was " + CONVERT(varchar, @Return) 22> GO Invalid Order Date Supplied Order Date was greater than 7 days old or was NULL. Correct the date and resubmit. Value Returned was -1000 1> 2> 3> drop PROC spInsertDateValidatedOrder; 4> GO 1> 2> drop table Orders; 3> GO</source>


How to Use RETURN

   <source lang="sql">

RETURN [<integer value to return>]

8> CREATE PROC spTestReturns 9> AS 10> DECLARE @MyMessage varchar(50) 11> DECLARE @MyOtherMessage varchar(50) 12> 13> SELECT @MyMessage = "Hi, " 14> PRINT @MyMessage 15> RETURN 16> SELECT @MyOtherMessage = "Sorry" 17> PRINT @MyOtherMessage 18> RETURN 19> 20> DECLARE @ReturnVal int 21> 22> EXEC @ReturnVal = spMySproc 23> 24> DECLARE @Return int 25> 26> EXEC @Return = spTestReturns 27> SELECT @Return 28> 29> drop PROC spTestReturns; 30> GO 1> 2> 3></source>


RETURN also allows for an optional integer expression:

   <source lang="sql">

RETURN [ integer_expression ] This integer value can be used in a stored procedure to communicate issues to the calling application.

Create a Stored Procedure that raises an error

9> CREATE PROCEDURE #usp_TempProc 10> AS 11> SELECT 1/0 12> RETURN @@ERROR 13> GO 1> 2> DECLARE @ErrorCode int 3> 4> EXEC @ErrorCode = #usp_TempProc 5> PRINT @ErrorCode 6> GO Msg 8134, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Procedure #usp_TempProc_________________________________ __________________________________________________________________________00000077, Line 11 Divide by zero error encountered. (0 rows affected) 8134</source>


Return a table from a function

   <source lang="sql">

2> 3> 4> CREATE TABLE titles( 5> title_id varchar(20), 6> title varchar(80) NOT NULL, 7> type char(12) NOT NULL, 8> pub_id char(4) NULL, 9> price money NULL, 10> advance money NULL, 11> royalty int NULL, 12> ytd_sales int NULL, 13> notes varchar(200) NULL, 14> pubdate datetime NOT NULL 15> ) 16> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/03/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/04/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/04/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/04/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/04/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/04/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> Create FUNCTION SalesByStore(@storid varchar(30)) 4> RETURNS TABLE 5> AS 6> RETURN (SELECT title, qty 7> FROM dbo.sales s, dbo.titles t 8> WHERE s.stor_id = @storid AND t.title_id = s.title_id) 9> GO 1> 2> 3> ALTER FUNCTION SalesByStore(@storid varchar(30)) 4> RETURNS TABLE 5> WITH SCHEMABINDING 6> AS 7> RETURN (SELECT title, qty 8> FROM dbo.sales s, dbo.titles t 9> WHERE s.stor_id = @storid AND t.title_id = s.title_id) 10> GO 1> 2> 3> SELECT obj_name = SUBSTRING(OBJECT_NAME(d.id), 1, 20), 4> dep_obj = SUBSTRING(OBJECT_NAME(d.depid), 1, 20), 5> col_name = SUBSTRING(name, 1, 15), 6> IsSchemaBound = CASE deptype 7> WHEN 1 THEN "Schema Bound" 8> ELSE "Free" 9> END 10> FROM 11> sysdepends d 12> JOIN syscolumns c ON d.depid = c.id 13> AND d.depnumber = c.colid 14> WHERE object_name(d.id) LIKE "SalesByStore%" 15> GO obj_name dep_obj col_name IsSchemaBound


-------------------- --------------- -------------

SalesByStore titles title_id Schema Bound SalesByStore titles title Schema Bound SalesByStore sales stor_id Schema Bound SalesByStore sales qty Schema Bound SalesByStore sales title_id Schema Bound (5 rows affected) 1> 2> drop FUNCTION SalesByStore; 3> GO 1> drop table sales; 2> drop table titles; 3> GO 1></source>


RETURN is used to exit the current Transact-SQL batch, query, or stored procedure immediately.

   <source lang="sql">

RETURN does not execute any code in the batch/query/procedure scope that follows after it. This example demonstrates how to use RETURN to unconditionally stop a query: 8> 9> CREATE TABLE employee( 10> id INTEGER NOT NULL PRIMARY KEY, 11> first_name VARCHAR(10), 12> last_name VARCHAR(10), 13> salary DECIMAL(10,2), 14> start_Date DATETIME, 15> region VARCHAR(10), 16> city VARCHAR(20), 17> managerid INTEGER 18> ); 19> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid


---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> IF NOT EXISTS 4> (SELECT ID FROM Employee) 5> BEGIN 6> RETURN 7> END 8> GO 1> 2> 3> drop table employee; 4> GO</source>


RETURNS a TABLE with structure

   <source lang="sql">

4> 5> 6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/03/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/04/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/04/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/04/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/04/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/10/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> CREATE FUNCTION SalesByStore_MS(@storid varchar(30)) 5> RETURNS @sales TABLE(title varchar(80), qty int) 6> AS 7> BEGIN 8> INSERT @sales 9> SELECT title, qty 10> FROM sales s, titles t 11> WHERE s.stor_id = @storid AND t.title_id = s.title_id 12> RETURN 13> END 14> GO 1> 2> drop FUNCTION SalesByStore_MS; 3> GO 1> drop table sales; 2> drop table titles; 3> GO 1> 2></source>


Return statement

   <source lang="sql">

8> Create PROC spTestReturns 9> AS 10> DECLARE @MyMessage varchar(50) 11> DECLARE @MyOtherMessage varchar(50) 12> 13> SELECT @MyMessage = "Hi, " 14> PRINT @MyMessage 15> RETURN 100 16> SELECT @MyOtherMessage = "Sorry" 17> PRINT @MyOtherMessage 18> RETURN 19> GO 1> 2> drop PROC spTestReturns; 3> GO 1> 2></source>