February 16, 2008 at 10:53 am
Following is a section of a bigger sproc that I had posted earlier. The post name was “BEGIN…END, IF EXISTS syntax error”.
Everything that I have read says that for every BEGIN, you must have an END. I believe I do in the code below.
If I disable the code with /* …*/, I receive no error messages when I hit F5. This tells me that any syntax errors that exist are in this section. I’ve put some notes in to help explain the process.
--Not every, step uses a prevailing wage (PW),
--this one does. BUT, the PW IS NOT stored in the TimeStudy table,
--it is stored in the JobSteps table.
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
BEGIN --first BEGIN
SELECT PrevailingWage
FROM --I need the PW to compute the workers wages for this step
tbl_Jobs_JobSteps
WHERE @StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
END --for first BEGIN
--Start my IF EXISTS. Following is the info I need from the
--TimeStudy table. It's telling me that "If there is
--a record in the TimeStudy table with a Client_PK_ID
--that matches the Client_PK_ID selected earlier in
--the sproc AND there is StepNumber_PK_ID that matches
--the StepNumber_PK_ID also selected earlier in
--the sproc, then give me the info for that record
--from the TimeStudy table.
--Pretty straight forward.
BEGIN ---2nd Begin
IF EXISTS(
SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
END ---for 2nd Begin
--But if there is no record in the TimeStudy table
--for the selected Client_PK_ID and the
--StepNumber_PK_ID above, then tell the user.
ELSE -- does not exist. tell user. Rollback.
BEGIN --3rd begin
SET @Message = 'No Time Study Record.' --rollback
END ---for 3rd begin
But when I remove the /*…*/ and enable the code, the I receive the following errors:
Msg 156, Level 15, State 1, Procedure TimeSheet_Insert_TimesheetEntry, Line 293
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Procedure TimeSheet_Insert_TimesheetEntry, Line 449
Incorrect syntax near 'end'.
Msg 156 is within the section that I disable/enable, Msg 102 is at the end of the complete sproc.
I have lost count of the numerous configurations of BEGIN…END that I have tried. Some have not thrown an error message, but none have ever given me the info from the TimeStudy table that I need.
Thanks for any help, tips or suggestions.
February 16, 2008 at 11:34 am
If you are going to break the code up like this then, you must address the begin correctly. A begin must proceed an if not prior to. You may have an if above where you are starting the 2nd begin I am not sure. Based on your prior code you did.
the code as posted should look like this:
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
BEGIN --first BEGIN
SELECT PrevailingWage
FROM --I need the PW to compute the workers wages for this step
tbl_Jobs_JobSteps
WHERE @StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
END --for first BEGIN
--Start my IF EXISTS. Following is the info I need from the
--TimeStudy table. It's telling me that "If there is
--a record in the TimeStudy table with a Client_PK_ID
--that matches the Client_PK_ID selected earlier in
--the sproc AND there is StepNumber_PK_ID that matches
--the StepNumber_PK_ID also selected earlier in
--the sproc, then give me the info for that record
--from the TimeStudy table.
--Pretty straight forward.
--no begin here <-----------------
IF EXISTS(
SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN ---2nd Begin is here <------------
--some code if exists
END ---for 2nd Begin
--But if there is no record in the TimeStudy table
--for the selected Client_PK_ID and the
--StepNumber_PK_ID above, then tell the user.
ELSE -- does not exist. tell user. Rollback.
BEGIN --3rd begin
SET @Message = 'No Time Study Record.' --rollback
END ---for 3rd begin
February 16, 2008 at 2:46 pm
Adam, I’ve worked though the code a bit and made a few small changes. But I am still having issues. Here is the current situation.
With the following, if @PayFormulaCode = ‘TS’, then it works as it should. If a client has a TS record, then it is selected. If they do not, then an error is given. This is good.
But if @PayFormulaCode (PFC) does NOT = ‘TS’ (such as AW, DT, etc.), then it goes to the ELSE statement. This might sound correct, but it is not. This bit of code is only for when the PFC = ‘TS’. The other codes have their own process. It’s as if the ELSE keyword is “outside” of the ‘PFC = TS’ bit of code.
---1st IF
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
BEGIN ---<<< First BEGIN
IF EXISTS( ---2nd IF
SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN --<< 2nd BEGIN
SELECT @TimeStudy_PK_ID =
tbl_Clients_ClientTimeStudy.TimeStudy_PK_ID,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent,
@PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage
FROM
tbl_Clients_ClientTimeStudy
JOIN
tbl_Jobs_JobSteps
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END --<<For 2nd BEGIN.
ELSE – Between the first BEGIN and first END.
BEGIN –3rd begin
set @Message = 'No Time Study Record.' --rollback
return 1
END --3rd end
END ---<<For first BEGIN.
If I change my BEGIN and ENDS to the following, I receive the same results. No syntax errors are generated.
For brevity in the following, I’ve taken out some of the code of the area in question.
---1st IF
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
--BEGIN ---<<<First BEGIN. Not being used
IF EXISTS( ---2nd IF
…..code taken out
)
BEGIN --<<Being used. SECOND BEGIN
SELECT …..code taken out
FROM
…..code taken out
ON
…..code taken out
END --<<Being used with Second BEGIN.
ELSE --
BEGIN --Third begin
set @Message = 'No Time Study Record.' --rollback
return 1
END --Third end
--END ---<<<Not being used.
When I change it to the following, then it allows someone without a timestudy record to be entered (it shouldn’t) and kills the sproc when anything other than a TS PFC is used (it shouldn’t).
---1st IF
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
BEGIN ---First begin. being used
IF EXISTS( ---2nd IF
SELECT …..code taken out
)
BEGIN –Second BEGIN. being used
SELECT …..code taken out
FROM
…..code taken out
ON
…..code taken out
END ---For second BEGIN.
END --for first begin above. moved from below the ELSE to here
ELSE --
BEGIN --Third begin
set @Message = 'No Time Study Record.' --rollback
return 1
END --Third end
--END ---not being used.
Thanks.
February 16, 2008 at 6:38 pm
I am not sure why this is not working right for you. I would reduce the number of nested IF by coding the block like this:
IF EXISTS(
SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
AND @PayFormulaCode = 'TS'
AND @Units = 0
OR @Units IS NULL
BEGIN
SELECT @TimeStudy_PK_ID =
tbl_Clients_ClientTimeStudy.TimeStudy_PK_ID,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent,
@PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage
FROM
tbl_Clients_ClientTimeStudy
JOIN
tbl_Jobs_JobSteps
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END
ELSE
IF NOT EXISTS(
SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
AND @PayFormulaCode = 'TS'
AND @Units = 0
OR @Units IS NULL.
BEGIN
set @Message = 'No Time Study Record.' --rollback
return 1
END
February 17, 2008 at 12:02 pm
Adam, I think we... excuse me...you, got it! I made a few very minor changes for my understanding and readability, but you got the logic. Thanks for your patience and help.
My code is below. I still need to do some error catching and other tasks, but I believe (hope, wish, beg) that the hardest part is over.
Does this forum have a star or point system? You deserve quite a few.
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
IF EXISTS(
SELECT *
FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN
SELECT
@TimeStudy_PK_ID =
tbl_Clients_ClientTimeStudy.TimeStudy_PK_ID,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent,
@PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage
FROM
tbl_Clients_ClientTimeStudy
JOIN
tbl_Jobs_JobSteps
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END
ELSE
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
IF NOT EXISTS(
SELECT *
FROM
tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN
SET @Message = 'No Time Study Record.'
--rollback
return 1
END
February 17, 2008 at 2:40 pm
Sweet, 😀
I am glad everything worked out and thanks for the feedback.
February 18, 2008 at 7:30 am
It seems like you need parentheses around the units part too. That's why your else statement ran even when @PayFormulaCode was not TS in one of the early versions.
declare @PayFormulaCode char(2), @Units int
select @PayFormulaCode = 'TS', @Units = 0
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
print 'is TS and no units' -- runs as expected
else
print 'did else'
select @PayFormulaCode = 'AB', @Units = null
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
print 'is TS and no units' -- runs not as expected
else
print 'did else'
IF @PayFormulaCode = 'TS' AND (@Units = 0 OR @Units IS NULL)
print 'is TS and no units'
else
print 'did else' -- runs as expected
February 18, 2008 at 2:21 pm
K,
Thanks for the input. Being a rookie at this, I must pat myself on the back. I saw that I needed the ()'s while working on the other part.
Thanks,
Bill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply