Incorrect syntax near the keyword 'end'.

Question

Post reply

Incorrect syntax near the keyword 'end'.

Sandy2704

SSCrazy

Points: 2156
More actions
December 6, 2010 at 2:03 pm

#229371

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??

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

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

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

sandippani SSC Eights! Points: 957 More actions · Answer 5

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

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

elads778 SSC Enthusiast Points: 142 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

elads778 SSC Enthusiast Points: 142 More actions · Answer 9

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.