1: | Name the six navigation operations that are possible with cursors. | ||||||||||||
A1: | The six operations are NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE. | ||||||||||||
2: | Can a cursor select data from joined tables? | ||||||||||||
A2: | Yes, a cursor can select data from a joined table. In addition, it can be declared for update as well. However, when updating a cursor like this, only the columns in the table listed in the UPDATE statements are affected. You can update columns in all the tables, but you must do it in separate UPDATE statements. | ||||||||||||
3: | Name the six steps required to use a cursor. | ||||||||||||
A3: | The six steps are
|
1: | Create a cursor on the Employees table in the Northwind database. Read in each employee's last name, birth date, and hire date. If an employee is more than 40 years old, then print his last name, and hire date.
use northwind declare crs_employees cursor for select lastname, birthdate, hiredate from employees order by lastname open crs_employees Declare @Emp_Name varchar(20), @Birth_Date datetime, @Hire_Date datetime, @tempstr varchar(50), @age int print 'Employee Name Birth Date Age Hire Date' print '--------------------- ---------- --- ---------' fetch next from crs_employees into @Emp_Name, @Birth_Date, @Hire_Date while @@fetch_status = 0 begin set @age = datediff(yy,@birth_date,getdate()) if @age > 40 set @tempstr = convert(char(22),@Emp_Name) + convert(char(13), @birth_date,101) + convert(char(4), @age) + convert(char(10), @hire_date,101) print @tempstr fetch next from crs_employees into @Emp_Name, @Birth_Date, @Hire_Date end close crs_employees deallocate crs_employees |
A1: | Results:
Employee Name Birth Date Age Hire Date --------------------- ---------- --- --------- Buchanan 03/04/1955 45 10/17/1993 Callahan 01/09/1958 42 03/05/1994 Davolio 12/08/1948 52 05/01/1992 Davolio 12/08/1948 52 05/01/1992 Fuller 02/19/1952 48 08/14/1992 Fuller 02/19/1952 48 08/14/1992 Fuller 02/19/1952 48 08/14/1992 Peacock 09/19/1937 63 05/03/1993 Peacock 09/19/1937 63 05/03/1993 |