SQL Server/T-SQL Tutorial/Transact SQL/Variable
Содержание
- 1 A SQL script that uses variables
- 2 A variable can be assigned a value from a subquery
- 3 Global variable names begin with an @@ prefix.
- 4 Local variables used for searches
- 5 Pass variable to a user-defined function
- 6 SELECT LEFT(@FullName, 5)
- 7 Testing the ISNULL function
- 8 Use aggregate function with variables
- 9 Use if statement to check a variable
- 10 Use Local Variables in where clause
- 11 Use one variables in like function
- 12 Use right function with a variable
- 13 Varchar type variable
- 14 Working with Variables
- 15 XML type variable
A SQL script that uses variables
<source lang="sql">
5> 6> 7> create table Billings ( 8> BankerID INTEGER, 9> BillingNumber INTEGER, 10> BillingDate datetime, 11> BillingTotal INTEGER, 12> TermsID INTEGER, 13> BillingDueDate datetime , 14> PaymentTotal INTEGER, 15> CreditTotal INTEGER 16> 17> ); 18> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> DECLARE @MaxBilling money, @MinBilling money 4> DECLARE @PercentDifference decimal(8,2) 5> DECLARE @BillingCount int, @BankerIDVar int 6> 7> SET @BankerIDVar = 95 8> SET @MaxBilling = (SELECT MAX(BillingTotal) FROM Billings 9> WHERE BankerID = @BankerIDVar) 10> SELECT @MinBilling = MIN(BillingTotal), @BillingCount = COUNT(*) 11> FROM Billings 12> WHERE BankerID = @BankerIDVar 13> SET @PercentDifference = (@MaxBilling - @MinBilling) / @MinBilling * 100 14> 15> PRINT "Maximum Billing is $" + CONVERT(varchar,@MaxBilling,1) + "." 16> PRINT "Minimum Billing is $" + CONVERT(varchar,@MinBilling,1) + "." 17> PRINT "Maximum is " + CONVERT(varchar,@PercentDifference) + 18> "% more than minimum." 19> PRINT "Number of Billings: " + CONVERT(varchar,@BillingCount) + "." 20> 21> GO
Number of Billings: 0. 1> 2> drop table Billings; 3> GO</source>
A variable can be assigned a value from a subquery
<source lang="sql">
7> CREATE TABLE authors( 8> au_id varchar(11), 9> au_lname varchar(40) NOT NULL, 10> au_fname varchar(20) NOT NULL, 11> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 12> address varchar(40) NULL, 13> city varchar(20) NULL, 14> state char(2) NULL, 15> zip char(5) NULL, 16> contract bit NOT NULL 17> ) 18> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> 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 rows affected) (1 rows affected) 1> 2> -- First batch using a straight SELECT: 3> DECLARE @firstname varchar(20) 4> SELECT @firstname = au_fname 5> FROM authors 6> WHERE au_lname = "Greene" 7> SELECT @firstname 8> GO
NULL (1 rows affected) 1> 2> -- Second batch using a subquery: 3> DECLARE @firstname varchar(20) 4> SELECT @firstname = ( SELECT au_fname 5> FROM authors 6> WHERE au_lname = "Greene") 7> SELECT @firstname 8> GO
NULL (1 rows affected) 1> 2> drop table authors; 3> GO 1></source>
Global variable names begin with an @@ prefix.
<source lang="sql">
5> Create table Eq (EqId int identity(1,1), 6> Make varchar(50), 7> Model varchar(50), 8> EqTypeId int) 9> GO 1> 2> Declare @intEqId int 3> Insert into Eq(Make, Model, EqTypeId) 4> Values ("ACME", "Turbo", 2) 5> Select @intEqId = @@identity 6> GO (1 rows affected) 1> 2> 3> 4> drop table Eq; 5> GO 1></source>
Local variables used for searches
<source lang="sql">
19> CREATE TABLE employee( 20> id INTEGER NOT NULL PRIMARY KEY, 21> first_name VARCHAR(10), 22> last_name VARCHAR(10), 23> salary DECIMAL(10,2), 24> start_Date DATETIME, 25> region VARCHAR(10), 26> city VARCHAR(20), 27> managerid INTEGER 28> ); 29> 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> 4> DECLARE @ID int 5> DECLARE @MinStandardCost money 6> DECLARE @MaxStandardCost money 7> 8> SET @MinStandardCost = 3396 9> SET @MaxStandardCost = 10000 10> 11> SELECT ID, Start_Date, salary 12> FROM Employee 13> WHERE Salary BETWEEN @MinStandardCost and @MaxStandardCost 14> 15> drop table employee; 16> GO ID Start_Date salary
----------------------- ------------
1 2005-03-22 00:00:00.000 5890.00
2 2003-07-21 00:00:00.000 4789.00
3 2001-12-01 00:00:00.000 6678.00
4 2006-03-03 00:00:00.000 5567.00
5 2004-07-02 00:00:00.000 4467.00
6 2002-05-19 00:00:00.000 6456.00
7 2008-03-18 00:00:00.000 5345.00
8 2007-07-17 00:00:00.000 4234.00
9 2001-04-16 00:00:00.000 6123.00
(9 rows affected)</source>
Pass variable to a user-defined function
<source lang="sql">
6> CREATE FUNCTION dbo.ufnIsOdd (@n int) 7> RETURNS bit 8> AS 9> BEGIN 10> RETURN (@n % 2) 11> END 12> GO 1> 2> DECLARE @numb int 3> SET @numb = 6 4> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1" 5> SET @numb = 7 6> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1" 7> GO Number Is_odd = 1
----------
6 0
(1 rows affected) Number Is_odd = 1
----------
7 1
(1 rows affected) 1></source>
SELECT LEFT(@FullName, 5)
<source lang="sql">
4> DECLARE @FullName VarChar(25) 5> SET @FullName = "George Washington" 6> SELECT LEFT(@FullName, 5) 7> GO
Georg (1 rows affected)</source>
Testing the ISNULL function
<source lang="sql">
4> 5> DECLARE @intTest int 6> SELECT ISNULL(@intTest,5) 7> SELECT @intTest = 37 8> SELECT ISNULL(@intTest,5) 9> GO
5
(1 rows affected)
37
(1 rows affected) 1></source>
Use aggregate function with variables
<source lang="sql">
3> CREATE TABLE roysched( 4> title_id varchar(20), 5> lorange int NULL, 6> hirange int NULL, 7> royalty int NULL 8> ) 9> GO 1> 2> 3> insert roysched values("1", 0, 10000, 10) 4> insert roysched values("2", 10001, 20000, 12) 5> insert roysched values("3", 20001, 30000, 14) 6> insert roysched values("4", 30001, 40000, 16) 7> insert roysched values("5", 40001, 50000, 18) 8> 9> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> DECLARE @min_range int, @hi_range int -- Variables declared 4> SELECT @min_range=MIN(lorange), 5> @hi_range=MAX(hirange) FROM roysched -- Variables assigned 6> SELECT @min_range, @hi_range -- Values of variables 7> -- returned as result 8> 9> GO
-----------
0 50000
(1 rows affected) 1> 2> drop table roysched; 3> GO</source>
Use if statement to check a variable
<source lang="sql">
4> CREATE TABLE #VersionTable( 5> [Index] int PRIMARY KEY, 6> Name varchar(30), 7> Internal_Value int, 8> Character_Value varchar(250) 9> ) 10> GO 1> 2> INSERT INTO #VersionTable 3> EXEC master..xp_msver 4> 5> DECLARE @Version int 6> 7> SELECT @Version = (SELECT Internal_Value 8> FROM #VersionTable 9> WHERE Name = "ProductVersion") 10> IF (@Version) >= 524288 11> PRINT "Running SS2K Beta 2 or Later" 12> ELSE 13> IF @Version >= 458752 14> PRINT "Running 7.0 RTM or Later" 15> ELSE 16> PRINT "Running Pre 7.0 RTM Version" 17> GO (20 rows affected) (20 rows affected) Running SS2K Beta 2 or Later 1> DROP TABLE #VersionTable 2> GO</source>
Use Local Variables in where clause
<source lang="sql">
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> 3> 4> DECLARE @limit money 5> SET @limit = $10 6> SELECT * FROM titles 7> WHERE price <= @limit 8> GO title_id title type pub_id price advance royalty ytd_sales notes
pubdate
-------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------
3 Emotional psychology 0736 7.9900 4000.0000 10 3336 Note 3
1991-06-1
2 00:00:00.000 (1 rows affected) 1> 2> drop table titles; 3> GO</source>
Use one variables in like function
<source lang="sql">
5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> DECLARE @alpha nchar(1) 4> SET @alpha = "b" 5> 6> 7> SELECT * 8> FROM Bankers 9> WHERE BankerName LIKE @alpha + "%" 10> GO BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected) 1> 2> drop table Bankers; 3> GO</source>
Use right function with a variable
<source lang="sql">
5> DECLARE @FullName VarChar(25) 6> SET @FullName = "George Washington" 7> SELECT RIGHT(@FullName, 5) 8> GO
ngton (1 rows affected)</source>
Varchar type variable
<source lang="sql">
5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20) 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 4> GO (1 rows affected) 1> 2> 3> 4> select * from employee; 5> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> 3> 4> DECLARE @Shifts varchar(20) 5> SET @Shifts = "" 6> SELECT @Shifts = @Shifts + s.first_Name + "," 7> FROM employee s 8> ORDER BY s.start_Date; 9> 10> SELECT @Shifts; 11> GO
Joan,James,Linda,Ali (1 rows affected) 1> 2> 3> 4> drop table employee; 5> GO</source>
Working with Variables
<source lang="sql">
Every variable that you use must be declared, together with its datatype, in a DECLARE statement. You can declare more than one variable at a time in a single DECLARE as long as you separate the variables with commas. Syntax for the DECLARE Statement DECLARE
{@local_variable [AS] data_type}
[,...n]</source>
XML type variable
<source lang="sql">
4> DECLARE @Book XML 5> SET @Book = 6> CAST("<Book name="SQL Server 2000 Fast Answers"> 7~ <Chapters> 8~ <Chapter id="1"> chapter 1 </Chapter> 9~ <Chapter id="2"> chapter 2 </Chapter> 10~ <Chapter id="3"> chapter 3 </Chapter> 11~ <Chapter id="4"> chapter 4 </Chapter> 12~ </Chapters> 13~ </Book>" as XML) 14> GO 1> 2></source>
