Solved

SQL BEGIN AND END BLOCK ISSUES/SYNTAX

Posted on 2014-09-03
9
176 Views
Last Modified: 2014-11-04
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
0
Comment
Question by:66chawger
9 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
 

Author Comment

by:66chawger
Comment Utility
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
 

Author Comment

by:66chawger
Comment Utility
Please excuse my grammar above or lack there of.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
>  Here you are simply nesting the IF statements, correct?
Logically yes, (if statement is inside the While condition )
0
 

Author Comment

by:66chawger
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
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
 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
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
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now