SQL Server/T-SQL Tutorial/Transact SQL/Set
Содержание
Assign the function result to the variable
5> DECLARE @MyNumber Int, @MyResult Int
6> SET @MyNumber = 144
7>
8> SET @MyResult = SQRT(@MyNumber)
9> GO
1>
Concatenating Variables
5> DECLARE @sql AS VARCHAR(MAX), @i AS INT;
6> SET @sql = "";
7> SET @i = 1;
8> WHILE @i <= 10
9> BEGIN
10> SET @sql = @sql + "PRINT " + CAST(@i AS VARCHAR(10))
11> + CHAR(13) + CHAR(10);
12> SET @i=@i+1;
13> END
14> EXEC(@sql);
15> GO
1
2
3
4
5
6
7
8
9
10
1>
Return the variable value
4>
5> DECLARE @MyNumber Int, @MyResult Int
6> SELECT @MyResult
7>
8> SET @MyNumber = 144
9> SET @MyResult = SQRT(@MyNumber)
10> GO
-----------
NULL
(1 rows affected)
1>
2>
SET variable and column in one command
3> CREATE TABLE titles(
4> title_id varchar(20),
5> title varchar(80) NOT NULL,
6> type char(12) NOT NULL,
7> pub_id char(4) NULL,
8> price money NULL,
9> advance money NULL,
10> royalty int NULL,
11> ytd_sales int NULL,
12> notes varchar(200) NULL,
13> pubdate datetime NOT NULL
14> )
15> 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> DECLARE @old_price money
4>
5> UPDATE titles
6> SET @old_price = price = price * 0.8
7> WHERE title_id = "PC2091"
8> GO
(0 rows affected)
1>
2> drop table titles;
3> GO
Syntax for Variable Assignment with the SET Statement
SET @local_variable = expression
Use set command to update a variable
8> CREATE TABLE Product(
9> ProductID int NOT NULL,
10> Name nvarchar(25) NOT NULL,
11> ProductNumber nvarchar(25) ,
12> Color nvarchar(15) NULL,
13> StandardCost money NOT NULL,
14> Size nvarchar(5) NULL,
15> Weight decimal(8, 2) NULL,
16> ProductLine nchar(20) NULL,
17> SellStartDate datetime NOT NULL,
18> SellEndDate datetime NULL
19> )
20> GO
1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22");
2> GO
(1 rows affected)
1>
2>
3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
4> GO
(1 rows affected)
1>
2>
3>
4>
5>
6>
7> DECLARE @Counter Int
8> SET @Counter = 1
9> WHILE @Counter < 4
10> BEGIN
11> PRINT "SubCategory "
12> SELECT Name, ProductID
13> FROM Product
14> WHERE ProductID = @Counter
15> SET @Counter = @Counter + 1
16> END
17> GO
SubCategory
Name ProductID
------------------------- -----------
Product A 1
(1 rows affected)
SubCategory
Name ProductID
------------------------- -----------
Product B 2
(1 rows affected)
SubCategory
Name ProductID
------------------------- -----------
Product C 3
(1 rows affected)
1>
2>
3> drop table Product;
4> GO
Using SET to Assign Variables
5> DECLARE @MyNumber Int, @MyResult Int
6> SET @MyNumber = 144
7> GO
1>