Comma has not been placed correctly as defined

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

Comma has not been placed correctly as defined

Postby nghinv » Thu Feb 25, 2010 5:07 am

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,
nghinv
 
Posts: 4
Joined: Wed Nov 18, 2009 7:55 am

Postby Anu Deshpande » Fri Feb 26, 2010 10:48 am

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: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby nghinv » Sat Feb 27, 2010 5:51 am

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

Code: Select all

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)

Code: Select all
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
Code: Select all
EXEC STP_ABC @param1, @param2, @param3, @param4, @param5, @param6, @param7,
    @param8, @param9, @param10, @param11, @param12, @param13, @param14,
    @param15, @param16, @param17, @param18, @param19, @param110


-- thanks
nghinv
 
Posts: 4
Joined: Wed Nov 18, 2009 7:55 am

Postby Anu Deshpande » Tue Mar 02, 2010 12:53 pm

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: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 1 guest

cron