Incorrect syntax near the keyword 'end'.

  • I am getting the error message(Msg 156, Level 15, State 1, Procedure selectMediCalProcedureCode, Line 14

    Incorrect syntax near the keyword 'end'.

    ) when i create the following stored procedure

    create procedure [dbo].[selectMediCalProcedureCode]

    @CodeID int

    as

    begin

    set nocount on

    select ProcedureID

    from hrp_CrosswalkProcedureCode

    where Entity = 'Medi-Cal'

    and ProcedureID in( select *

    from cln_ServiceInstance

    where ProcedureCodeID = @CodeID

    end

    I couldn't figure out where the errror message was....May be i m overlooking it....Can anyone help me solve this issue??

  • Hi

    where ProcedureCodeID = @CodeID)

    )-- is Missed

    Dont just post the code with out crossverfying it is simple and basic step, you should be able to solve.

    Thanks

    Parthi

    Thanks
    Parthi

  • Your 'in' clause has an opening paren but no closing.


    And then again, I might be wrong ...
    David Webb

  • One other point.

    IN (SELECT * ....

    That will fail if there's more than one column in that table. Fix it to specify the exact column used for the IN.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • parthi-1705 (12/6/2010)


    Hi

    where ProcedureCodeID = @CodeID)

    )-- is Missed

    Dont just post the code with out crossverfying it is simple and basic step, you should be able to solve.

    Thanks

    Parthi

    Be gentle Parthi, we all have those days. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • create procedure [dbo].[selectMediCalProcedureCode]

    @CodeID int

    as

    begin

    set nocount on

    select ProcedureID

    from hrp_CrosswalkProcedureCode

    where Entity = 'Medi-Cal'

    and ProcedureID in( select * -- explicitly specify the column name if you have mutiple columns in cln_ServiceInstance table

    from cln_ServiceInstance

    where ProcedureCodeID = @CodeID)--is missed

    end

  • Craig Farrell (12/6/2010)

    Be gentle Parthi, we all have those days. 🙂

    Hi Craig

    We can help on things which could not be solved at minimum time or when it is critical here this is just syntax error we should think more then one time before we post a code.Just have a relax and once again go through the code then if you find the error then post.I did not say dont post just cross verify and then post.if he cant able to solve this basic then he will be posting for small small errors.The heading itself shows Incorrect syntax error so better have more than one time look at query and then post that is my advice.

    Thanks

    Parthi

    Thanks
    Parthi

  • I have a kinda similar problem

    I'm new to SQL and I'm now working on MS SQL 2000.

    I've created an SP which will in one case to an insert and in another - update. I get the error in the last 'END' word.

    CREATE PROCEDURE Insert_EditCoupon

    @ID Int = 0,

    @Name NVarChar(150),

    @DueDate DateTime,

    @DiscountType SmallInt,

    @DiscountRule SmallInt,

    @DiscountValidation SmallInt,

    @DiscountAmount Float,

    @ProductID Int,

    @MinimumPrice Float,

    @SiteID Int,

    @CouponCode BigInt,

    @TimesToUse Int

    AS

    IF @ID < 1

    BEGIN

    DECLARE @dt DATETIME

    SET @dt = GETDATE()

    INSERT INTO tblCoupons

    (@ID,

    @Name,

    @DueDate,

    @DiscountType,

    @DiscountValidation,

    1,

    @ProductID,

    @DiscountAmount,

    @MinimumPrice,

    @dt,

    @SiteID,

    0,

    @CouponCode,

    @TimesToUse)

    END

    ELSE

    BEGIN

    UPDATE tblCoupons

    SET

    Name = @Name,

    DueDate = @DueDate,

    DiscountType = @DiscountType,

    DiscountValidation = @DiscountValidation,

    IsPublish = 1,

    ProductID = @ProductID,

    DiscountAmount = @DiscountAmount,

    MinimumPrice = @MinimumPrice,

    CouponCode = @CouponCode,

    TimesToUse = @TimesToUse

    WHERE ID = @ID

    END

    GO

    I would much appreciate your help since I'm currently stuck.

  • You have two BEGINs (one at the start of the procedure and one after the ELSE) and only one END.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I fixed it.

    there was nothing wrong with the if's.

    first problem is I didn't write all the fields in the INSER INTO <table> (...All Fields...)

    second is I didn't had the VALUES keywork.

    the result is I should have wrote it like this:

    INSER INTO <table> (...All Fields...) VALUES (...All Values According To Fields...)

    This would have been fixed sooner, it's just that I'm tired.

    but thanks for all the help

    Development Architecture.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply