ALTER procedure [dbo].[Sp_addbusdetails]
@bus_id nvarchar(50),
@tour_date nvarchar(50),
@tour_id nvarchar(50),
@ndays int,
@dsal nvarchar(50),
@csal nvarchar(50),
@dieselcharge nvarchar(50),
@ocharge nvarchar(50)
as
declare @count int
if(@ndays=1)
begin
select @count=COUNT(*) from Busdetails where tour_date=@tour_date and bus_id=@bus_id
if @count=0
insert into busdetails(tour_id,tour_date,bus_id,ndays,dsal,csal,dieselcharge,ocharge) values(@tour_id,@tour_date,@bus_id,@ndays,@dsal,@csal,@dieselcharge,@ocharge)
end
else
begin
set @count = 0;
while @count < @ndays
begin
insert into busdetails
(tour_id,
tour_date,
bus_id,
ndays,
dsal,
csal,
dieselcharge,
ocharge
)
values
(REPLACE(@tour_id,
CONVERT(char(10), @tour_date, 120),
CONVERT(char(10), DATEADD(DAY, @count, @tour_date), 120)),
DATEADD(DAY, @count, @tour_date),
@bus_id,
@ndays,
@dsal,
@csal,
@dieselcharge,
@ocharge);
set @count = @count + 1;
end
end