If Begin End Block - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If Begin End Block

Author

Topic

kkellestine
Starting Member

3 Posts

Posted - 2012-08-18 : 11:59:04

I am trying to add some code to a stored procedure and the following code block fails with a syntax error:

IF @Req_Event = 'PLC_SP_1200_A'
BEGIN
set @Option_Nbr = 11
Select @Expected_Scan = Value
From ISS_cfg_Item_Option
Where Item_Nbr = @Job_Dtl_Item_Nbr
AND Option_Nbr = @Option_Nbr

if @@rowCount = 0
Begin
Set @Req_Value = '0'
goto done
End

if @Expected_Scan = 'BZM'
Set @Req_Value = '1'
Else if @Expected_Scan = 'BZL'
Set @Req_Value = '2'
Else if @Expected_Scan = 'CKL'
Set @Req_Value = '3'
ELSE
Set @Req_Value = '0'
goto done
END

The error is 'Incorrect syntax near' the last line of code in the stored procedure. If I comment out the first BEGIN statement after the IF I am able to update my stored procedure.

I don't understand why I can not use IF Begin End to create a conditional block.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 12:09:48

few things

1. I cant see done block as specified by goto statement
2. you're not wrapping code beneath each ELSE so keep in mind that only next statement gets associated with ELSE and all following statements will get executed regardless of condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kkellestine
Starting Member

3 Posts

Posted - 2012-08-18 : 12:20:34

"done:" is at the bottom of the stored procedure. If I reduce the new block of code I'm trying to add to just this:
IF @Req_Event = 'PLC_SP_1200_A'
BEGIN
set @Option_Nbr = 11
Select @Expected_Scan = Value
From ISS_cfg_Item_Option
Where Item_Nbr = @Job_Dtl_Item_Nbr
AND Option_Nbr = @Option_Nbr

if @@rowCount = 0
Begin
Set @Req_Value = '0'
goto done
End
END

I still get the error. If I get rid of the first BEGIN the error goes away.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 12:52:32

your code can be simplified as


IF @Req_Event = 'PLC_SP_1200_A'
BEGIN	
set @Option_Nbr	 = 11

Select @Expected_Scan	 = Value
From ISS_cfg_Item_Option
Where Item_Nbr = @Job_Dtl_Item_Nbr
AND Option_Nbr = @Option_Nbr

Select @Req_Value = CASE @Expected_Scan
                         WHEN 'BZM'  THEN '1'
                         WHEN 'BZL'  THEN '2'
                         WHEN 'CKL'  THEN '3'
                         ELSE '0'
                  END       
goto done
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kkellestine
Starting Member

3 Posts

Posted - 2012-08-18 : 13:28:27

Thanks for the help. The CASE cleaned my code up nicely.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 15:20:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources