December 6, 2010 at 2:03 pm
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??
December 6, 2010 at 2:30 pm
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
December 6, 2010 at 2:30 pm
Your 'in' clause has an opening paren but no closing.
December 6, 2010 at 2:39 pm
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
December 6, 2010 at 3:44 pm
parthi-1705 (12/6/2010)
Hiwhere 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. 🙂
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
December 6, 2010 at 11:51 pm
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
December 7, 2010 at 12:11 am
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
September 17, 2011 at 2:35 am
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.
September 17, 2011 at 4:20 am
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
September 17, 2011 at 4:29 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply