SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 If Begin End Block		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kkellestine
Starting Member

3 Posts
Posted - 08/18/2012 :  11:59:04  Show Profile  Reply with Quote
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

India
47157 Posts
Posted - 08/18/2012 :  12:09:48  Show Profile  Reply with Quote
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/
Go to Top of Page

kkellestine
Starting Member

3 Posts
Posted - 08/18/2012 :  12:20:34  Show Profile  Reply with Quote
"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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 08/18/2012 :  12:52:32  Show Profile  Reply with Quote
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/
Go to Top of Page

kkellestine
Starting Member

3 Posts
Posted - 08/18/2012 :  13:28:27  Show Profile  Reply with Quote
Thanks for the help. The CASE cleaned my code up nicely.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 08/18/2012 :  15:20:35  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000