SQLServer存储过程

最基础的存储过程

新建存储过程

CREATE PROCEDURE PR_Sum12
AS
BEGIN
update formtable_main_122 set fzybcp = '4' WHERE requestid = 26275
END
go

修改存储过程

ALTER PROCEDURE PR_Sum12
AS
BEGIN
update formtable_main_122 set fzybcp = '6' WHERE requestid = 26275
END
go

执行存储过程

exec PR_Sum12

 

 

进阶存储过程

修改存储过程

ALTER PROCEDURE judge_MachineType  
@requestid int   
AS
    declare   @machineSumOne int,
   @machineSumTwo int
BEGIN    
    declare my_cursor_one cursor for
        SELECT COUNT(*) as machineSum FROM formtable_main_122_dt1 dt1 
        WHERE dt1.mainid = 
                ( SELECT id FROM formtable_main_122 main WHERE main.requestid = @requestid )     
        AND dt1.machineType!= 0
    declare my_cursor_Two cursor for
        SELECT COUNT(*)  as machineSum FROM formtable_main_122_dt3 dt3 
        WHERE        dt3.mainid = 
                ( SELECT main.id FROM formtable_main_122 main WHERE main.requestid = @requestid )         
        AND dt3.machineType!=0 
    open my_cursor_one
    open my_cursor_Two
    fetch next from my_cursor_one into @machineSumOne
    fetch next from my_cursor_Two into @machineSumTwo
    IF @machineSumOne > 0 or  @machineSumTwo >0
    begin
        update formtable_main_122 set fzybcp = '14' WHERE requestid = @requestid;
    end
    close my_cursor_one
    deallocate my_cursor_one            
    close my_cursor_Two
    deallocate my_cursor_Two           
END
GO

执行存储过程

exec judge_MachineType 26275