SQL Server/T-SQL Tutorial/Transact SQL/Return
Содержание
- 1 Assign returned value from a function to a declared variable
- 2 How to Use RETURN
- 3 RETURN also allows for an optional integer expression:
- 4 Return a table from a function
- 5 RETURN is used to exit the current Transact-SQL batch, query, or stored procedure immediately.
- 6 RETURNS a TABLE with structure
- 7 Return statement
Assign returned value from a function to a declared variable
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
How to Use RETURN
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>
RETURN also allows for an optional integer expression:
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
Return a table from a function
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>
RETURN is used to exit the current Transact-SQL batch, query, or stored procedure immediately.
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
RETURNS a TABLE with structure
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>
Return statement
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>