proc

--判断数据是否存在记录存在修改，否则插入

create proc HotLines_UpdateOrInsert
    
    @StartCity char(3)='',
    @EndCity char(3)='',
     @PriceDay1 money,
    @PriceDay2 money,
    @PriceDay3 money,
    @PriceDay4 money,
    @NumDay1 tinyint,
    @NumDay2 tinyint,
    @NumDay3 tinyint,
    @NumDay4 tinyint
as
    Declare @count int
--赋值
    set @count=0
begin
    if(@StartCity is not null and @EndCity is not null)
        select @count=count(*) 
        from HotLines 
        where DepartureCity=@StartCity and DestinationCity=@EndCity
    if(@count<=0)
        Insert into HotLines
        (
            DepartureCity,
            DestinationCity,
            PriceDay1,
            PriceDay2,
            PriceDay3,
            PriceDay4,
            NumDay1,
            NumDay2,
            NumDay3,
            NumDay4
        ) 
        values
        (
            @StartCity,
            @EndCity,
            @PriceDay1,
            @PriceDay2,
            @PriceDay3,
            @PriceDay4,
            @NumDay1,
            @NumDay2,
            @NumDay3,
            @NumDay4
        )
    else
        Update HotLines set 
            PriceDay1=@PriceDay1,
            PriceDay2=@PriceDay2,
            PriceDay3=@PriceDay3,
            PriceDay4=@PriceDay4,
            NumDay1=@NumDay1,
            NumDay2=@NumDay2,
            NumDay3=@NumDay3,
            NumDay4=@NumDay4
        where DepartureCity=@StartCity and DestinationCity=@EndCity
    
end

GO