SQL BEGIN AND END BLOCK ISSUES/SYNTAX

I have the following WHILE loop for my cursor... this is just the WHILE block...  

Trouble I am having is with BEGIN and END blocks... need to nest them, or another solution if someone wants to reorg this snippet.   I have lots of stored procs for various TSQL parsing, updating, etc., etc...  

This is a one time only situation I am working on, I may incorporate into a stored proc at a later time, but not at the immediate.

Don't like nexting BEGIN END blocks like this, but I am half way there.  I've gone back and forth with nesting a BEGIN/END TRANSACTION within a BEGIN/END block..  See in bold below.

  WHILE CHARINDEX(':', @valueList, @pos+1)>0
        BEGIN
            set @len = CHARINDEX(':', @valueList, @pos+1) - @pos
            set @value = SUBSTRING(@valueList, @pos, @len)

            PRINT @counter
            BEGIN
                   IF @counter > 0
                      set @counter = 0
                  

                             " Right here when the counter is > 0, I want to place SQL update logic.
                             "i.e.     Update tableA
                             "         SET columnXYZ = @value
                             "         Where......


                   END    
             
                  
            PRINT @value
                   
            set @pos = CHARINDEX(':', @valueList, @pos+@len) +1
            set @counter = @counter + 1
        END
     
        FETCH NEXT FROM tableA_cursor
        INTO @supplier, @valueList  

    END
66chawgerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
There is no problem with nesting BEGIN ... END blocks.  Or with using transactions within (nested) BEGIN ... END blocks.  Just make sure you do issue a commit or rollback for every transaction, since you may get unwanted/unexpected results if you don't.
0
Aneesh RetnakaranDatabase AdministratorCommented:
There is no issue nesting the begin..end


BEGIN
                    IF @counter > 0
                       set @counter = 0
                   

                               if @counter > 0
                                BEGIN     Update tableA
                                        SET columnXYZ = @value
                                       Where......
                              END
                    END    
               
                   
             PRINT @value
                     
             set @pos = CHARINDEX(':', @valueList, @pos+@len) +1
             set @counter = @counter + 1
         END
0
66chawgerAuthor Commented:
Scott/Aneesh, I have coded this way for years... some SQL fanatics tend to try to over-code or compete with different methods based on timings and such... which is ok, with large amounts of data, performance can be of imperative.

I think possibly, it my issue may have been more frustration with syntax than with the actual question of nesting or not nesting of BEGIN/END blocks...   Maybe I am just having a bad day, I tried several different times to nest earlier and kept getting syntax errors in SSMS.    

Aneesh, let me try what you have above.... I had originally started another BEGIN/END block where you have have illustrated the second IF for checking the @counter.   Her you are simply nesting the IF statements, correct?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

66chawgerAuthor Commented:
Please excuse my grammar above or lack there of.
0
Aneesh RetnakaranDatabase AdministratorCommented:
>  Here you are simply nesting the IF statements, correct?
Logically yes, (if statement is inside the While condition )
0
66chawgerAuthor Commented:
Ok,

Below is what I attempted... but again having syntax issues.  The section in bold is what is giving me syntactical errors with the blocks.

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @Supplier
        set @pos = 0
        set @len = 0
        set @counter = 0

        WHILE CHARINDEX(':', @valueList, @pos+1)>0
        BEGIN
            set @len = CHARINDEX(':', @valueList, @pos+1) - @pos
            set @value = SUBSTRING(@valueList, @pos, @len)
            --SELECT @pos, @len, @value /*this is here for debugging*/
            PRINT @counter
            BEGIN
--             PRINT @counter
                      IF @counter > 0
                           set @counter = 0
                           IF @counter >0
                                    BEGIN
                                        UPDATE USER_DEFINED36_FW
                                        SET SERVICE_COST_FW = @value
                                        WHERE SUPPLIER_FW = '010563762 02'
                                          AND PATINDEX('%CONVENT%', @desc) ! = 0
                                    END        
                               END      
                      ELSE
                           set @desc = ''
                           set @desc = @value  
                      END

                  END    
             
                  
                  PRINT @value
                   
                  
                  set @pos = CHARINDEX(':', @valueList, @pos+@len) +1
                  set @counter = @counter + 1
        END
     
        FETCH NEXT FROM user_defined8_fw_cursor
        INTO @supplier, @valueList  

    END

CLOSE user_defined8_fw_cursor
DEALLOCATE user_defined8_fw_cursor
0
Aneesh RetnakaranDatabase AdministratorCommented:
If you align them properly it will be easy, or else you can put the comments with your begin end


WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Supplier
    set @pos = 0
    set @len = 0
    set @counter = 0

    WHILE CHARINDEX(':', @valueList, @pos+1)>0
    BEGIN
        set @len = CHARINDEX(':', @valueList, @pos+1) - @pos
        set @value = SUBSTRING(@valueList, @pos, @len)
        --SELECT @pos, @len, @value /*this is here for debugging*/
        PRINT @counter
        BEGIN
--             PRINT @counter
                  IF @counter > 0
                        set @counter = 0
                  IF @counter >0
                  BEGIN
                        UPDATE USER_DEFINED36_FW
                        SET SERVICE_COST_FW = @value
                        WHERE SUPPLIER_FW = '010563762 02'
                        AND PATINDEX('%CONVENT%', @desc) ! = 0
                  END   -- if @Counter    
                  ELSE
                  BEGIN
                        set @desc = ''
                        set @desc = @value  
                  END -- else
            END
      END  -- While WHILE CHARINDEX(':



END       ---WHILE @@FETCH_STATUS = 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alpesh PatelAssistant ConsultantCommented:
WHILE CHARINDEX(':', @valueList, @pos+1)>0
BEGIN
            set @len = CHARINDEX(':', @valueList, @pos+1) - @pos
            set @value = SUBSTRING(@valueList, @pos, @len)

            PRINT @counter
                   IF @counter > 0
                   BEGIN
                             set @counter = 0
                             Update tableA
                             SET columnXYZ = @value
                             Where......
                   END    
             
                 
            PRINT @value
                   
            set @pos = CHARINDEX(':', @valueList, @pos+@len) +1
            set @counter = @counter + 1

        FETCH NEXT FROM tableA_cursor
        INTO @supplier, @valueList  

END
0
DcpKingCommented:
Firstly your bolded block will never execute the update path, because you only do it if @Counter > 0, but you set @Counter to zero immediately before it !

Next, you've missed some code at the top, because you end more blocks than you open in the code as shown.

Finally, to show code neatly here you can use a pseudo-html markup, where the tags before and after the block are "code" and "/code" and the carets normally used in HTML are replaced by square brackets. It makes code much easier to read!

hth

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.