Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Question Unanswered: WHILE..CONTINUE Problem

    Hi all,
    I need a bit of assistance with a piece of code I have written. I keep receiving a Syntax error "Incorrect syntax near END". The procedure is ~1000 line of code so I have just cut and paste the "problem" sql here and attached the full code to this message to see if anyone can help me out. Cheers


    -- BEGIN CODE
    IF @NoOfChildren > 0
    BEGIN
    DECLARE @childNo AS INTEGER
    DECLARE @SQL AS VARCHAR(8000)
    SET @childNo = 1

    WHILE @childNo != @NoOfChildren
    BEGIN
    -- Perform Dynamic SQL check for children
    SET @SQL = 'IF @strChild'+ @childNo + 'Surname IS NOT NULL
    BEGIN
    SET @asciicounter = 1
    SET @asciifound = 0

    WHILE @asciicounter != 255
    BEGIN
    IF CHARINDEX(char(@asciicounter),@strChild'+ @childNo +'Surname) != 0
    BEGIN
    IF @asciicounter NOT IN (32,45,46)
    BEGIN
    SET @asciifound = 1
    END
    END
    SET @asciicounter = @asciicounter + 1
    CONTINUE
    END
    IF @asciifound = 1
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''45'''',''
    END
    END'
    EXECUTE @SQL

    SET @SQL = ''

    -- Perform Dynamic SQL check for Forename
    SET @SQL = 'IF @strChild'+ @childNo + 'Forename IS NOT NULL
    BEGIN
    SET @asciicounter = 1
    SET @asciifound = 0

    WHILE @asciicounter != 255
    BEGIN
    IF CHARINDEX(char(@asciicounter),@strChild'+ @childNo +'Forename) != 0
    BEGIN
    IF @asciicounter NOT IN (32,45,46)
    BEGIN
    SET @asciifound = 1
    END
    END
    SET @asciicounter = @asciicounter + 1
    CONTINUE
    END
    IF @asciifound = 1
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''46'''',''
    END
    END'
    EXECUTE @SQL

    SET @SQL = ''

    -- Perform Dynamic SQL check on DOB

    SET @SQL = 'IF @dtChild'+@childNo+'Dob IS NULL
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''047'',''
    END

    IF ISDATE(@dtChild'+@childNo+'Dob) = 0
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''048'',''
    END

    IF DATEDIFF(YEAR, @dtChild'+@childNo+'Dob, GETDATE()) NOT BETWEEN 0 AND 18
    BEGIN
    IF DATEDIFF(YEAR, @dtChild'+@childNo+'Dob, GETDATE()) > 18
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''049'',''
    END
    ELSE
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''050'',''
    END'
    EXECUTE @SQL

    SET @SQL = ''

    -- Perform dynamic SQL check on Gender

    SET @SQL = 'IF @blnChild'+@childNo+'Gender IS NULL
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''051'',''
    END'

    EXECUTE @SQL

    SET @childNo = @childNo + 1
    CONTINUE
    END

    -- END CODE
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    One too many block "Begin" statements is most likely the problem. Count how many BEGIN/END pairs you have. But I also wonder why you had so many in the first place?

    Plus look at this code snippet:

    SET @SQL = 'IF @blnChild'+@childNo+'Gender IS NULL
    BEGIN
    SET @strErrorBucket = @strErrorBucket + ''''051'',''
    END'
    What do you see wrong or possibly an error?

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    Hmm... To my knowledge that is the way I should be doing things, but if I am wrong please re-educate me. It was working fine until I had to use the last WHILE loop then just syntax errors because of what needs to be done I need to run dynamic sql and I have not had to much experience doing that.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Like I said, count the number of BEGIN/END blocks. I think you need another END or two?

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by corncrowe
    Like I said, count the number of BEGIN/END blocks. I think you need another END or two?
    Yea you are right.
    I haven't gone through the whole code, but putting 2 'END's at the bottom makes it a valid code...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    I count 16 ENDS and 18 BEGIN. You'll need one END for each BEGIN.

  7. #7
    Join Date
    Nov 2005
    Posts
    5
    I feel like a moron but thanks for the help there guys. Have been staring at this code for a while.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Megaman, you desperately need to find someone who knows how to write SQL code to help you with this. There are so many problems with the code you posted that I cannot begin to imagine what the rest of the 1000 lines looks like.

    You can't "EXECUTE @SQL". You have to issue the command like this: "EXEC (@SQL)", and the you will get errors stating that the variables in your dynamic SQL are undeclared because variables exist only within the scope of the code that creates them and dynamic SQL executes outside the scope of the calling code.

    This is just one of the problems I see here. The biggest problem is that you do not seem to understand the concept of set-based SQL coding. All your WHILE loops and dynamic SQL are bad practices for database coding.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2005
    Posts
    5
    Thanks for the heads up. I will have to research better ways of doing what I am trying to achieve.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •