Use Local Variables in where clause : Variable « Transact SQL « SQL Server / T-SQL Tutorial






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








20.2.Variable
20.2.1.Working with Variables
20.2.2.Use right function with a variable
20.2.3.Testing the ISNULL function
20.2.4.SELECT LEFT(@FullName, 5)
20.2.5.Global variable names begin with an @@ prefix.
20.2.6.XML type variable
20.2.7.Varchar type variable
20.2.8.Use if statement to check a variable
20.2.9.A variable can be assigned a value from a subquery
20.2.10.Use aggregate function with variables
20.2.11.Use Local Variables in where clause
20.2.12.A SQL script that uses variables
20.2.13.Local variables used for searches
20.2.14.Pass variable to a user-defined function
20.2.15.Use one variables in like function