SQL Refactor ignores the XML WITH when laying out fields

Refactors and formats SQL code.

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

SQL Refactor ignores the XML WITH when laying out fields

Postby jonathan.baggaley » Fri May 21, 2010 10:41 am

I have the following stored procedure (ignore the lack of normalization - the data is imported from a spreadsheet and gets broken down later! :roll: ) which, when I lay out should break all the XML WITH columns to shorter lines or at least break them at the 120 character point since wrapping is on too. Is this a bug/design \"feature\" or have I missed something in the SQL Refactor options?

    sql refactor v1.3.0.207
    wrapping: 120chars
    subquery placement max length 80
    column placements - not ticked
    schema statements.definition - all ticked
    parenthasis placement - all new line


Code: Select all
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:      
-- Create date: 19 may 10
-- Description:   Batch save imported quit attempt smoking lines
-- =============================================
ALTER PROCEDURE [Smoking].[usp_BatchSaveSmokingLines]
    (
      @BatchMasterId INT,
      @SmokingLinesDoc TEXT
    )
AS
    BEGIN
        DECLARE @iDoc INT,
            @MaxArchivedByVersion INT,
            @ModifiedDate DATETIME

        CREATE TABLE #tmpUpdateValues
            (
              QuitAttemptId INT,
              BatchMasterId INT,
              ClientId INT,
              VenueId INT,
              AdviserId INT,
              PeriodId INT,
              Surname VARCHAR(50),
              FirstName VARCHAR(50),
              Title VARCHAR(20),
              Address VARCHAR(500),
              PostCode VARCHAR(10),
              NHSNumber VARCHAR(50),
              DaytimeTel VARCHAR(30),
              MobileTel VARCHAR(30),
              AltContactNumber VARCHAR(30),
              DateOfBirth DATETIME,
              AgeYears INT,
              GenderCode INT,
              PresciptionChargeExempt BIT,
              Pregnant BIT,
              BreastFeeding BIT,
              AnyKnownAllergies VARCHAR(50),
              HistoryOfCardioVasc BIT,
              CardioVascDetails VARCHAR(50),
              OccupationId INT,
              EthnicCode VARCHAR(3),
              TimeToFirstSmokeId INT,
              AvgNumberSmokedPerDay VARCHAR(50),
              HeardAboutId INT,
              HeardAboutOther VARCHAR(50),
              AgreedQuitDate DATETIME,
              DateOfLastTobaccoUse DATETIME,
              DateOf4WeekFollowUp DATETIME,
              InterventionTypeId INT,
              InterventionTypeOther VARCHAR(50),
              PharmSupportId1 INT,
              PharmSupportDesc1 VARCHAR(10),
              PharmSupportId2 INT,
              PharmSupportDesc2 VARCHAR(10),
              PharmSupportId3 INT,
              PharmSupportDesc3 VARCHAR(10),
              PharmSupportId4 INT,
              PharmSupportDesc4 VARCHAR(10),
              TreatmentOutcomeId INT,
              NameOfGP VARCHAR(50),
              PracticeCode VARCHAR(10),
              SubmittedDate DATETIME,
              CreatedBy UNIQUEIDENTIFIER,
              CreatedDate DATETIME,
              LastModifiedDate DATETIME,
              LastModifiedBy UNIQUEIDENTIFIER,
              StatusId INT,
              SubmittedInPeriodId INT,
              Active BIT,
              DepartmentWard VARCHAR(30),
              LocationSetting VARCHAR(30),
              ContactTelNo VARCHAR(30),
              AdviserCodeRef VARCHAR(30)
            )

        CREATE TABLE #tempDiary
            (
              ParentWorkingId INT,
              DiaryContactId INT,
              QuitAttemptId INT,
              ContactDate DATETIME,
              COReading VARCHAR(50),
              TypeOfContact VARCHAR(50),
              NRTVoucherNo VARCHAR(15),
              Comments VARCHAR(100)
            )

        SET @ModifiedDate = GETDATE()

   --Create an internal representation of the XML document.  If doc not valid will fail gracefully
        EXEC sp_xml_preparedocument @idoc OUTPUT, @SmokingLinesDoc
--
        INSERT  INTO #tmpUpdateValues
                ( QuitAttemptId, BatchMasterId, ClientId, VenueId, AdviserId, PeriodId, Surname, FirstName, Title,
                  Address, PostCode, NHSNumber, DaytimeTel, MobileTel, AltContactNumber, DateOfBirth, AgeYears,
                  GenderCode, PresciptionChargeExempt, Pregnant, BreastFeeding, AnyKnownAllergies, HistoryOfCardioVasc,
                  CardioVascDetails, OccupationId, EthnicCode, TimeToFirstSmokeId, AvgNumberSmokedPerDay, HeardAboutId,
                  HeardAboutOther, AgreedQuitDate, DateOfLastTobaccoUse, DateOf4WeekFollowUp, InterventionTypeId,
                  InterventionTypeOther, PharmSupportId1, PharmSupportDesc1, PharmSupportId2, PharmSupportDesc2,
                  PharmSupportId3, PharmSupportDesc3, PharmSupportId4, PharmSupportDesc4, TreatmentOutcomeId, NameOfGP,
                  PracticeCode, SubmittedDate, CreatedBy, CreatedDate, LastModifiedDate, LastModifiedBy, StatusId,
                  SubmittedInPeriodId, Active, DepartmentWard, LocationSetting, ContactTelNo, AdviserCodeRef )
                SELECT  *
                FROM    OPENXML (@idoc, '/SmokingBatch/DetailItems/SmokingRow',1) WITH ( QuitAttemptId INT 'QuitAttemptId', BatchMasterId INT 'BatchMasterId', ClientId INT 'ClientId', VenueId INT 'VenueId', AdviserId INT 'AdviserId', PeriodId INT 'PeriodId', Surname VARCHAR(50) 'Surname', FirstName VARCHAR(50) 'FirstName', Title VARCHAR(20) 'Title', Address VARCHAR(500) 'Address', PostCode VARCHAR(10) 'PostCode', NHSNumber VARCHAR(50) 'NHSNumber', DaytimeTel VARCHAR(30) 'DaytimeTel', MobileTel VARCHAR(30) 'MobileTel', AltContactNumber VARCHAR(30) 'AltContactNumber', DateOfBirth DATETIME 'DateOfBirth', AgeYears INT 'AgeYears', GenderCode INT 'GenderCode', PresciptionChargeExempt BIT 'PresciptionChargeExempt', Pregnant BIT 'Pregnant', BreastFeeding BIT 'BreastFeeding', AnyKnownAllergies VARCHAR(50) 'AnyKnownAllergies', HistoryOfCardioVasc BIT 'HistoryOfCardioVasc', CardioVascDetails VARCHAR(50) 'CardioVascDetails', OccupationId INT 'OccupationId', EthnicCode VARCHAR(3) 'EthnicCode', TimeToFirstSmokeId INT 'TimeToFirstSmokeId', AvgNumberSmokedPerDay VARCHAR(50) 'AvgNumberSmokedPerDay', HeardAboutId INT 'HeardAboutId', HeardAboutOther VARCHAR(50) 'HeardAboutOther', AgreedQuitDate DATETIME 'AgreedQuitDate', DateOfLastTobaccoUse DATETIME 'DateOfLastTobaccoUse', DateOf4WeekFollowUp DATETIME 'DateOf4WeekFollowUp', InterventionTypeId INT 'InterventionTypeId', InterventionTypeOther VARCHAR(50) 'InterventionTypeOther', PharmSupportId1 INT 'PharmSupportId1', PharmSupportDesc1 VARCHAR(10) 'PharmSupportDesc1', PharmSupportId2 INT 'PharmSupportId2', PharmSupportDesc2 VARCHAR(10) 'PharmSupportDesc2', PharmSupportId3 INT 'PharmSupportId3', PharmSupportDesc3 VARCHAR(10) 'PharmSupportDesc3', PharmSupportId4 INT 'PharmSupportId4', PharmSupportDesc4 VARCHAR(10) 'PharmSupportDesc4', TreatmentOutcomeId INT 'TreatmentOutcomeId', NameOfGP VARCHAR(50) 'NameOfGP', PracticeCode VARCHAR(10) 'PracticeCode', SubmittedDate DATETIME 'SubmittedDate', CreatedBy UNIQUEIDENTIFIER 'CreatedBy', CreatedDate DATETIME 'CreatedDate', LastModifiedDate DATETIME 'LastModifiedDate', LastModifiedBy UNIQUEIDENTIFIER 'LastModifiedBy', StatusId INT 'StatusId', SubmittedInPeriodId INT 'SubmittedInPeriodId', Active BIT 'Active', DepartmentWard VARCHAR(30) 'DepartmentWard', LocationSetting VARCHAR(30) 'LocationSetting', ContactTelNo VARCHAR(30) 'ContactTelNo', AdviserCodeRef VARCHAR(30) 'AdviserCodeRef' )      


        SELECT  *
        FROM    #tmpUpdateValues

        SELECT  *
        FROM    OPENXML (@idoc, '/SmokingBatch/DetailItems/SmokingRow/DiaryEntriesRows/SmokingDiaryEntry',1) WITH ( ParentWorkingId INT 'ParentWorkingId', DiaryContactId INT 'DiaryContactId', QuitAttemptId INT 'QuitAttemptId', ContactDate DATETIME 'ContactDate', COReading VARCHAR(50) 'COReading', TypeOfContact VARCHAR(50) 'TypeOfContact', NRTVoucherNo VARCHAR(15) 'NRTVoucherNo', Comments VARCHAR(100) 'Comments', CreatedBy UNIQUEIDENTIFIER 'CreatedBy', CreatedDate DATETIME 'CreatedDate', LastModifiedDate DATETIME 'LastModifiedDate', LastModifiedBy UNIQUEIDENTIFIER 'LastModifiedBy' )
      





--DetailId INT 'CategoryDetailId', PlannedActivity money 'PlannedActivity', ActualActivityQ1 money 'ActualActivityQ1', ActualActivityQ2 money 'ActualActivityQ2', ActualActivityQ3 money 'ActualActivityQ3', ActualActivityQ4 money 'ActualActivityQ4', CalculatedTotal money 'CalculatedResult', ActualEarningsTotal money 'ActualEarningsTotal'
--)



    END



:roll:
jonathan.baggaley
 
Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire

Postby Brian Donahue » Tue May 25, 2010 4:54 pm

Hi Jonathan,

We're aware that OPENXML...WITH does not wrap and have opened a development issue for this with an ID of SR-568. Part of the problem is that we haven't decided on a style for the wrapping, so if you have any suggestions, this would be a good time!
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

wrapping...

Postby jonathan.baggaley » Tue May 25, 2010 5:11 pm

Multiple options
> Each field set on a different line (commas before or after)
> Line length - any field sets crossing the line (e.g. 120 chars) get wrapped
> Leave unchanged

Options how far to indent below the OPENXML

Hope this helps!

thanks

Jon
:-)zz[
jonathan.baggaley
 
Posts: 5
Joined: Tue Apr 20, 2010 4:56 pm
Location: Berkshire


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests