Error in else condition in stored procedure

Posted by Klbaiju under Sql Server on 6/9/2014 | Points: 10 | Views : 470 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts