Solved

SQL BEGIN AND END BLOCK ISSUES/SYNTAX

Posted on 2014-09-03
9
182 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 500 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

Industry Leaders: 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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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