?
Solved

SQL BEGIN AND END BLOCK ISSUES/SYNTAX

Posted on 2014-09-03
9
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40301966
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
ID: 40301970
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
ID: 40302026
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:66chawger
ID: 40302027
Please excuse my grammar above or lack there of.
0
 
LVL 75

Expert Comment

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

Author Comment

by:66chawger
ID: 40302117
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 2000 total points
ID: 40302140
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
ID: 40302852
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
ID: 40307452
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 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