Error showing in stored procedure while using condition [Resolved]

Posted by Klbaiju under Sql Server on 8/26/2016 | Points: 10 | Views : 214 | Status : [Member] | Replies : 1
Hi,

Following is my stored procedure

Create procedure [dbo].[USp_Routeselconductor]
@tourdate nvarchar(50),
@busid nvarchar(50),
@uname nvarchar(50)
as
declare @conductorid nvarchar(50)
select @conductorid=conductor_id2 from routeBusdetails where tour_date=@tourdate and bus_id=@busid and uname=@uname

if(@conductorid <>0)
begin
select conductor_id,cname from routeconductormaster where uname=@uname and conductor_id not in(select conductor_id from routeBusdetails where uname=@uname and tour_date = @tourdate and conductor_id is not null
union all
select conductor_id2 from routeBusdetails where uname=@uname and tour_date =@tourdate and conductor_id2 is not null)
end
else
begin
select conductor_id,cname from routeconductormaster where uname=@uname and conductor_id not in(select conductor_id from routeBusdetails where uname=@uname and tour_date = @tourdate and conductor_id is not null
end

select c.cname,b.startid,b.ndays as ndays from routeconductormaster c,routebusdetails b where c.conductor_id=b.conductor_id2 and b.uname=@uname and tour_date=@tourdate and b.bus_id=@busid


when i execute iam getting an error like this

Msg 156, Level 15, State 1, Procedure Sp_Routeselconductor, Line 19
Incorrect syntax near the keyword 'end'.

what is the error and how to solve


Regards

Baiju




Responses

Posted by: Rajnilari2015 on: 8/26/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
You missed

a) the initial begin.. end.
b) forgot to close the bracket in the else part like

....................
...................
where uname=@uname and tour_date = @tourdate and conductor_id is not null)

) was missing

Point # (b) was the real cause of the issue.

Now try this

Create procedure [dbo].[USp_Routeselconductor]

@tourdate nvarchar(50),
@busid nvarchar(50),
@uname nvarchar(50)
as

BEGIN

declare @conductorid nvarchar(50)
select @conductorid=conductor_id2 from routeBusdetails where tour_date=@tourdate and bus_id=@busid and uname=@uname

if(@conductorid <>0)
begin

select conductor_id,cname
from routeconductormaster
where uname=@uname
and conductor_id not in(select conductor_id
from routeBusdetails
where uname=@uname and tour_date = @tourdate and conductor_id is not null
union all
select conductor_id2
from routeBusdetails
where uname=@uname and tour_date =@tourdate and conductor_id2 is not null)
end
else
begin

select conductor_id,cname
from routeconductormaster
where uname=@uname
and conductor_id not in(select conductor_id
from routeBusdetails
where uname=@uname and tour_date = @tourdate and conductor_id is not null)
end

select c.cname,b.startid,b.ndays as ndays
from routeconductormaster c,routebusdetails b
where c.conductor_id=b.conductor_id2
and b.uname=@uname
and tour_date=@tourdate
and b.bus_id=@busid
END


Hope this helps

--
Thanks & Regards,
RNA Team

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

Login to post response