Storage Process for MSSQL employees to increase salaries

Add a salary to an employee. when half of the employees do not reach 6000 yuan, add 100 to all employees and print the salary.

Code

  --  Add a salary to an employee. when half of the employees do not reach 6000 yuan, add 100 to all employees and print the salary.  
--  Author: [DBA] Xiaoqi  
  Create    Table     [  # T ]  (ID  Int  , Name  Char  (  10  ), Sal  Int  )  --  Create temporary table  
  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  

(The number of affected rows is  9  Rows)

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  

(The number of affected rows is  9  Rows)

  500  





 

 

-- If you are interested, try changing the salary of 6000 to a parameter. It should be very simple.

Custom Search