Comma has not been placed correctly as defined

nghinv

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,

Anu D

Many thanks for your post.

Can you kindly let us know steps to reproduce the problem will be really helpful?

nghinv

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 D

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.