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>