mssql员工增加工资的存储过程

--给员工加工资，当员工里面有一半的人没有达到6000元的时候，所有员工加100，并打印加了多少工资
--author：【DBA】小七
create  table [#t](id int, name char(10),sal int)  --创建临时表
insert into #t 
select 1,'alex',1500 union all
select 2,'kelly',5000 union all
select 3,'lily',10000 union all
select 4,'judy',6000 union all
select 5,'tom',5900 union all
select 6,'cherly',4000 union all
select 7,'cherly',3500 union all
select 8,'romeo',7000 union all
select 9,'frank',5500
select * from #t

go

if Exists(Select name From sysobjects Where name='add_sal' And type='P')
Drop Procedure add_sal
     Go

create proc add_sal
as
begin
set nocount on
declare @count1 float,@count2 int,@up_sal int
set @up_sal=0
set @count1=(select count(*) from #t)
set @count2=(select count(8) from #t where sal<6000)
while(@count2>(@count1/2))
begin
update #t set sal=sal+100 
set @count1=(select count(*) from #t)
set @count2=(select count(8) from #t where sal<6000)
set @up_sal=@up_sal+100
end
print  @up_sal
set nocount off 
end
go

exec add_sal
go

drop table #t

go

（所影响的行数为 9 行）

id          name       sal         
----------- ---------- ----------- 
1           alex       1500
2           kelly      5000
3           lily       10000
4           judy       6000
5           tom        5900
6           cherly     4000
7           cherly     3500
8           romeo      7000
9           frank      5500

（所影响的行数为 9 行）

500