SQL Server/T-SQL Tutorial/Transact SQL/Set
Содержание
Assign the function result to the variable
<source lang="sql">
5> DECLARE @MyNumber Int, @MyResult Int 6> SET @MyNumber = 144 7> 8> SET @MyResult = SQRT(@MyNumber) 9> GO 1></source>
Concatenating Variables
<source lang="sql">
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></source>
Return the variable value
<source lang="sql">
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></source>
SET variable and column in one command
<source lang="sql">
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</source>
Syntax for Variable Assignment with the SET Statement
<source lang="sql">
SET @local_variable = expression</source>
Use set command to update a variable
<source lang="sql">
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</source>
Using SET to Assign Variables
<source lang="sql">
5> DECLARE @MyNumber Int, @MyResult Int 6> SET @MyNumber = 144 7> GO 1></source>
