error in this stored procedure, plz help, a challange for me.

Question

I am writing this sp but it has an error and I am not getting what is error. It a sort of challange for me, kindly help me.
--------------------------------------
Description.
this sp receive a user id, on its based it has to fetch records from database. Is boat column (true/ false) tells it is a record of boat or an engine. If boat then it get its model and manufacture as well, other wise just get engine name from engine table. plz guide.
-------------------------------------



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetBoat_Comments]
    @BoatID int=0,
    @MessageCode int=1,
    @MemberID int,
    @Mode int
AS
Begin
-- Mode 1, Get All messages of a member of everytype.
if(@Mode=1)
    begin
        select mm.id, mm.FName +mm.LName as Name,mm.email,mm.Message,
                case mm.IsBoat
                when 'True' then (brands.Name + Models.Name)
                else Engines.Name
                end as ItemName
                 
        from Member_Messages mm,Brands,Models,Engines

        where mm.MemberId=@MemberId
              and ((mm.IsBoat='True') and (mm.ItemId=Member_Boats.Id))
              and Member_Boats.BrandId=Brand.Id
              and Member_Boats.ModelId=Model.Id
              and ((mm.IsBoat='False') and (mm.ItemId=engines.Id));
           
       
       

End

Answer 1

well.. you could at least tell us what is the error message

in your stored procedure, you reference Member_Boats, i don't see that table in your FROM clause

for bit column when comparing, use IsBoat = 1 for True, IsBoat = 0 for false

---

KH Tan

Answer 2

I guess you are missing an "END" for the BEGIN Statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetBoat_Comments]
    @BoatID int=0,
    @MessageCode int=1,
    @MemberID int,
    @Mode int
AS
Begin
-- Mode 1, Get All messages of a member of everytype.
if(@Mode=1)
    begin
        select mm.id, mm.FName +mm.LName as Name,mm.email,mm.Message,
                case mm.IsBoat
                when 'True' then (brands.Name + Models.Name)
                else Engines.Name
                end as ItemName
                 
        from Member_Messages mm,Brands,Models,Engines

        where mm.MemberId=@MemberId
              and ((mm.IsBoat='True') and (mm.ItemId=Member_Boats.Id))
              and Member_Boats.BrandId=Brand.Id
              and Member_Boats.ModelId=Model.Id
              and ((mm.IsBoat='False') and (mm.ItemId=engines.Id));
           
     END 
       

End

---

Be the change you want.