Error showing in stored procedure while using condition [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts