sqlserver存储过程的纠结问题

Question

create database YGGL
use YGGL
 create table Employees (
  EmployeeID char(6) primary key not null,
 Name varchar(6) not null,
 Education varchar(4) not null,
 Birthday datetime not null,
 Sex bit not null default 1,
 WorkYear tinyint null,
 Address  varchar(40),
 PhoneNumber char(12) null,
  Pepartment char(3) not null 
)insert into Employees values('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668',2);
insert into Employees values('010008','伍容华','本科','1976-03-28',1,3,'北京东路100-2','83321321',1);
insert into Employees values('020010','王向容','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361',1);
insert into Employees values('020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301',1);
insert into Employees values('102201','刘明','本科','1972-10-18',1,3,'虎距路100-2','83606608',5);
insert into Employees values('102208','朱骏','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817',5);
insert into Employees values('108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722',3);
insert into Employees values('111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418',5);
insert into Employees values('210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336',3);
insert into Employees values('302566','李玉珉','本科','1968-09-20',1,3,'热和路209-3','58765991',4);
insert into Employees values('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901',4);
insert into Employees values('504209','陈林琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158',4);
select * from Employees create table Departments(
  DepartmentID int not null primary key identity(1,1),
  DepartmentName char(20) not null,
  Note varchar(100) null
)insert into Departments values(1,'财务部',null);
insert into Departments values(2,'人力资源部',null);
insert into Departments values (3,'经理办公室',null);
insert into Departments values(4,'研发部',null);
insert into Departments values(5,'市场部',null);create table Salary(
  EmployeeID char(6) primary key not null,
  InCome  float not null,
  OutCome float not null,
)
  
 insert into Salary values('000001',2100.8,123.09)
 insert into Salary values('010008',1582.62,88.03);
 insert into Salary values('102201',2569.88,185.65);
 insert into Salary values('111006',1987.01,79.58);
 insert into Salary values('504209',2066.15,108.0);
 insert into Salary values('302566',2980.7,210.2);
 insert into Salary values('108991',3259.98,281.52);
 insert into Salary values('020010',2860.0,198.0);
 insert into Salary values('020018',2347.68,180.0);
 insert into Salary values('308759',2531.98,199.08);
 insert into Salary values('210678',2240.0,121.0);
 insert into Salary values('102208',1980.0,100.0);--(a)创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作

Answer 1

 
 Create proc Auto_Pepartment
 as
update a
set Pepartment=b.DepartmentID
 from Employees as a
inner join Departments as b on b.DepartmentName='经理办公室' and a.Pepartment<>b.DepartmentID
 where a.WorkYear>6

Answer 2

update Employees
set Pepartment = n.DepartmentID
from Employees m,  Departments n
where m.WorkYear >= 6 and n.DepartmentName = '经理办公室'select * from Employees/*
EmployeeID Name   Education Birthday                                               Sex  WorkYear Address                                  PhoneNumber  Pepartment 
---------- ------ --------- ------------------------------------------------------ ---- -------- ---------------------------------------- ------------ ---------- 
000001     王林     大专        1966-01-23 00:00:00.000                                1    8        中山路32-1-508                              83355668     3  
010008     伍容华    本科        1976-03-28 00:00:00.000                                1    3        北京东路100-2                                83321321     1  
020010     王向容    硕士        1982-12-09 00:00:00.000                                1    2        四牌楼10-0-108                              83792361     1  
020018     李丽     大专        1960-07-30 00:00:00.000                                0    6        中山东路102-2                                83413301     3  
102201     刘明     本科        1972-10-18 00:00:00.000                                1    3        虎距路100-2                                 83606608     5  
102208     朱骏     硕士        1965-09-28 00:00:00.000                                1    2        牌楼巷5-3-106                               84708817     5  
108991     钟敏     硕士        1979-08-10 00:00:00.000                                0    4        中山路10-3-105                              83346722     3  
111006     张石兵    本科        1974-10-01 00:00:00.000                                1    1        解放路34-1-203                              84563418     5  
210678     林涛     大专        1977-04-02 00:00:00.000                                1    2        中山北路24-35                                83467336     3  
302566     李玉珉    本科        1968-09-20 00:00:00.000                                1    3        热和路209-3                                 58765991     4  
308759     叶凡     本科        1978-11-18 00:00:00.000                                1    2        北京西路3-7-52                               83308901     4  
504209     陈林琳    大专        1969-09-03 00:00:00.000                                0    5        汉中路120-4-12                              84468158     4  （所影响的行数为 12 行）*/

Answer 3

本帖最后由 roy_88 于 2011-11-16 23:11:23 编辑

  

Answer 4

大侠呀  计算比例那里运行无法通过呀  说是Education not in(N'大专',N'高中not附近有语法错误

Answer 5

create  procedure p_Bili @b_l float output,@Z_y char(4) 
AS
BEGIN
declare @counts int
declare @num  int
declare @X_EM_ID char(6)
declare @E_U char(4)
declare @sum1 int
set @sum1=0
set @counts=(select count(*) from Employees)
SET @num=(select count(*) from Employees)
declare C_education cursor for
select EmployeeID,education from Employees 
open  C_education
fetch C_education into  @X_EM_ID,@E_U
while @counts>0
begin
set @counts=@counts-1
IF @E_U!=@Z_y
set @sum1=@sum1+1
fetch C_education into @X_EM_ID,@E_U
end
set @b_l= @sum1*1.0/@num
close C_education
deallocate C_education 
END
declare @b_2 float 
declare @Z_y char(4)
set  @Z_y='大专'
exec p_Bili @b_2 output,@Z_y 
select  @b_2 as '本科以上比例'
drop procedure p_Bili