VB - SQL 'Select' statement formatter/checker-VBForums
Results 1 to 30 of 30

Thread: VB - SQL 'Select' statement formatter/checker

Threaded View

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,112

    VB - SQL 'Select' statement formatter/checker

    The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
    This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.

    As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.

    Example usage:
    VB Code:
    1. Dim sSQL as String
    2.   sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
    3.   sSQL = FormatSelect(sSQL)
    4.   MsgBox sSQL

    A couple of examples of the inputs/outputs of the function:
    1) if the following Select statement is provided as input:
    Code:
    SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3
    ...it gets converted into this:
    Code:
    SELECT    Table1.Field1
    
    FROM      Table1
    
    WHERE     Field2 = 3

    2) if this Select statement is provided as input:
    Code:
    SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO' 
    WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
    From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
    Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005' 
    OR Table1.Field7 = '1/20/2005' 
    AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))
    ...it gets converted into this:
    Code:
    SELECT    Table1.Field1 AS Field1,
              Table1.Field2 AS Field2,
              CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
              IIf ( Field4 = 1, 1, 0 )
    
    FROM      Table1
              LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
              LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
    
    WHERE     Table1.Field5 Between '1/1/2005' AND '1/20/2005'
              AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
              OR Table1.Field7 = '1/20/2005'
              AND Table1.Field8 IN (
    
                        SELECT    Field1
                        
                        FROM      table4
                        
                        WHERE     field2 IN (
                        
                                            SELECT    Field1
                                            
                                            WHERE     a = b
                                            
                                            FROM      table2
                        
                                  )
    ...with the following warning message:
    Code:
    (in sub-query: 
      (in sub-query: 
        Incorrect section ordering: FROM should be before WHERE
        )
      )
    Note that the blank lines are optional, and can be disabled like this:
    VB Code:
    1. sSQL = FormatSelect(sSQL, False)

    The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:
    VB Code:
    1. sSQL = FormatSelect(sSQL, , 4, 2)

    An alternative version by CVMichael can be found here: VB - SQL Select Formatter.


    Please let me know if there are any parts of SQL syntax which are not handled appropriately.
    Attached Files Attached Files
    Last edited by si_the_geek; Nov 17th, 2005 at 03:57 PM. Reason: updated attachment and examples (see my posts below for reasons)

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.