Error in else condition in stored procedure

Posted by Klbaiju under Sql Server on 6/9/2014 | Points: 10 | Views : 358 | Status : [Member] | Replies : 1
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




Responses

Posted by: kgovindarao523-21772 on: 6/9/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,
try like this.

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
BEGIN


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
BEGIN
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;

END

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
END


Thank you,
Govind

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response