Comma has not been placed correctly as defined

nghinvnghinv Posts: 6
Hi,

There is a problem with commas position when apply SQL layout.

I've configured to place commas at the beginning of source code lines but when I apply the format to the source code commas position for EXECUTE, FETCH INTO variables (CURSOR)... statements have not been applied correctly.

please investigate.

thanks & best regards,

Comments

  • Many thanks for your post.

    Can you kindly let us know steps to reproduce the problem will be really helpful?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • 1. In SQL Refactor options, set the following item:
    (Setting 1) SET Place Comma At Start
    (Setting 2) SET [Column Placement] = [On New Line]
    2. Apply the SQL layout for the following code
    IF EXISTS ( SELECT
                    *
                FROM
                    sysobjects
                WHERE
                    name = 'STP_ABC' ) 
        DROP PROC STP_ABC
    GO
    CREATE PROC STP_ABC
    (
      @param1 VARCHAR(50)
    , @param2 VARCHAR(50)
    , @param3 VARCHAR(50)
    , @param4 VARCHAR(50)
    , @param5 VARCHAR(50)
    , @param6 VARCHAR(50)
    , @param7 VARCHAR(50)
    , @param8 VARCHAR(50)
    , @param9 VARCHAR(50)
    , @param10 VARCHAR(50)
    , @param11 VARCHAR(50)
    , @param12 VARCHAR(50)
    , @param13 VARCHAR(50)
    , @param14 VARCHAR(50)
    , @param15 VARCHAR(50)
    , @param16 VARCHAR(50)
    , @param17 VARCHAR(50)
    , @param18 VARCHAR(50)
    , @param19 VARCHAR(50)
    , @param110 VARCHAR(50)
    )
    AS 
    BEGIN
        CREATE TABLE #test
        (
          col1 VARCHAR(50)
        , col2 VARCHAR(50)
        , col3 VARCHAR(50)
        , col4 VARCHAR(50)
        , col5 VARCHAR(50)
        , col6 VARCHAR(50)
        , col7 VARCHAR(50)
        , col8 VARCHAR(50)
        , col9 VARCHAR(50)
        , col10 VARCHAR(50)
        , col11 VARCHAR(50)
        , col12 VARCHAR(50)
        , col13 VARCHAR(50)
        , col14 VARCHAR(50)
        , col15 VARCHAR(50)
        , col16 VARCHAR(50)
        , col17 VARCHAR(50)
        , col18 VARCHAR(50)
        , col19 VARCHAR(50)
        , col110 VARCHAR(50)
        )
        DECLARE CS CURSOR
            FOR SELECT TOP 10
                    *
                FROM
                    #test
        OPEN CS
        FETCH NEXT FROM CS INTO @param1, @param2, @param3, @param4, @param5,
            @param6, @param7, @param8, @param9, @param10, @param11, @param12,
            @param13, @param14, @param15, @param16, @param17, @param18, @param19,
            @param110 
    
        select
            @param1
          , @param2
          , @param3
          , @param4
          , @param5
          , @param6
          , @param7
          , @param8
          , @param9
          , @param10
          , @param11
          , @param12
          , @param13
          , @param14
          , @param15
          , @param16
          , @param17
          , @param18
          , @param19
          , @param110 
        close CS
        Deallocate CS
        DROP TABLE #test
        
    END
    
    Go 
    
    DECLARE
        @param1 VARCHAR(50)
      , @param2 VARCHAR(50)
      , @param3 VARCHAR(50)
      , @param4 VARCHAR(50)
      , @param5 VARCHAR(50)
      , @param6 VARCHAR(50)
      , @param7 VARCHAR(50)
      , @param8 VARCHAR(50)
      , @param9 VARCHAR(50)
      , @param10 VARCHAR(50)
      , @param11 VARCHAR(50)
      , @param12 VARCHAR(50)
      , @param13 VARCHAR(50)
      , @param14 VARCHAR(50)
      , @param15 VARCHAR(50)
      , @param16 VARCHAR(50)
      , @param17 VARCHAR(50)
      , @param18 VARCHAR(50)
      , @param19 VARCHAR(50)
      , @param110 VARCHAR(50)
    
    EXEC STP_ABC @param1, @param2, @param3, @param4, @param5, @param6, @param7,
        @param8, @param9, @param10, @param11, @param12, @param13, @param14,
        @param15, @param16, @param17, @param18, @param19, @param110 
    

    3. You can see that commas have not been placed correctly as expected (Seting 1 and Seting 2)
    FETCH NEXT FROM CS INTO @param1, @param2, @param3, @param4, @param5,
            @param6, @param7, @param8, @param9, @param10, @param11, @param12,
            @param13, @param14, @param15, @param16, @param17, @param18, @param19,
            @param110
    
    and
    EXEC STP_ABC @param1, @param2, @param3, @param4, @param5, @param6, @param7,
        @param8, @param9, @param10, @param11, @param12, @param13, @param14,
        @param15, @param16, @param17, @param18, @param19, @param110
    

    -- thanks
  • Anu DAnu D Posts: 876 Silver 3
    Many thanks for your detailed explanation I have added this (Setting to be provided for formatting Exec statements and cursors ) as a feature request in our internal tracking system.

    Tracking Id for this is SR-876.

    It will be reviewed for a future release of SQL Refactor although we have no timescales for this at present.

    Kindly let us know if you have any other issues or questions regarding the product,I'll like to help.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
Sign In or Register to comment.