SQL Server/T-SQL/Transact SQL/Assign Value — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:20, 26 мая 2010

Assign query result to a variable

2> create table employee(
3>     ID          int,
4>     name        nvarchar (10),
5>     salary      int,
6>     start_date  datetime,
7>     city        nvarchar (10),
8>     region      char (1))
9> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2>
3> /* First, declare a variable to hold the table name. Remember,
4~ ** object names can be 128 characters long
5~ */
6> DECLARE @myName varchar(128)
7>
8> -- Now, grab the table name that goes with our ID
9>
10> SELECT @myName = name
11>    FROM Employee
12>    WHERE ID = 1
13> PRINT @myName
14> GO
Jason
1>
2>
3> drop table employee
4> GO
1>
2>



Assign value from select statement to a variable

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2>
3> DECLARE @Test money
4>
5> SET @Test = (SELECT MAX(Salary) FROM Employee)
6> SELECT @Test
7> GO
---------------------
           90620.0000
(1 rows affected)
1>
2>
3> drop table employee
4> GO
1>



Dynamically set the number of rows returned

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2> --Dynamically set the number of rows returned.
3>
4> DECLARE @n int
5> SET @n = 6
6>
7> SELECT TOP (@n) ID, Name FROM Employee
8> GO
ID          Name
----------- ----------
          1 Jason
          2 Robert
          3 Celia
          4 Linda
          5 David
          6 James
(6 rows affected)
1>
2> drop table employee
3> GO
1>



Using SELECT to Assign Variables

1> -- Using SELECT to Assign Variables
2>
3> DECLARE @MyNumber1 Int, @MyNumber2 Int, @MyResult1 Int, @MyResult2 Int
4>
5> SELECT @MyNumber1 = 144, @MyNumber2 = 121
6> -- Assign the function result to the variable:
7>
8> SELECT @MyResult1 = SQRT(@MyNumber1), @MyResult2 = SQRT(@MyNumber2)
9> -- Return the variable value
10> SELECT @MyResult1, @MyResult2
11> GO
----------- -----------
         12          11
(1 rows affected)
1>
2>