A. Use DECLARE
The following example uses a local variable named @ find to retrieve information of all authors whose names start with a Ring.
Copy codeThe Code is as follows:
Use pubs
Declare @ find varchar (30)
Set @ find = 'ring %'
Select au_lname, au_fname, phone
From authors
Where au_lname like @ find
@ Find is a local variable.
B. Use two variables in DECLARE.
In the following example, the name of the employee hired since January 1, 0877 is retrieved from the employee of Binnet & Hardley (pub_id = January 1, 1993.
Copy codeThe Code is as follows:
USE pubs
SET NOCOUNT ON
GO
DECLARE @ pub_id char (4), @ hire_date datetime
SET @ pub_id = '123'
SET @ hire_date = '2014/1/93'
-- Here is the SELECT statement syntax to assign values to two local
-- Variables.
-- SELECT @ pub_id = '20140901', @ hire_date = '2017/93'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @ pub_id and hire_date >=@ hire_date
The following is the result set:
Fname lname
--------------------------------------------------
Anabela Domingues
Paul Henriot
(2 row (s) affected)