I'm using ApexSQL Refactor to format my SQL within Visual Studio and SSMS and I've almost got it configured the way I want it with a few exceptions.
How do I get leading commas to line up with the SELECT keyword rather than putting the comma in the first column? This is what I want:
CREATE PROCEDURE [Product].[usp_ManufacturerPart_SEL_ByVendorPartID]
(
@VendorPartID INT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
PS.ProductSizeID AS ManufacturerPartID
, PS.GenderID
, PS.GenericSizeID
, PS.ManufacturerDescription
, PS.ManufacturerID
, ISNULL(PS.Description, MS.ManufacturerSizeDescription) AS ManufacturerSizeDescription
, MS.ManufacturerSizeID
, PS.Quantity AS NewQuantity
, PS.ParentID
, PS.PreOrderQuantity
, MS.Size
, PS.Sku
FROM
Purchasing.VendorPart AS vp WITH(NOLOCK)
JOIN Purchasing.ManufacturerPartToVendorPart AS mptvp WITH(NOLOCK)
ON vp.VendorPartID = mptvp.VendorPartID
JOIN dbo.tblProductSize AS PS WITH(NOLOCK)
ON PS.ProductSizeID = mptvp.ManufacturerPartID
LEFT JOIN Product.ManufacturerSize AS MS WITH(NOLOCK)
ON PS.ManufacturerSizeID = MS.ManufacturerSizeID
WHERE
mptvp.VendorPartID = @VendorPartID
END
The best I could do with the settings is this (Lists > Columns > Format > Place each item on new line > Place comma before each item > Do not indent comma):
CREATE PROCEDURE [Product].[usp_ManufacturerPart_SEL_ByVendorPartID]
(
@VendorPartID INT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
PS.ProductSizeID AS ManufacturerPartID
, PS.GenderID
, PS.GenericSizeID
, PS.ManufacturerDescription
, PS.ManufacturerID
, ISNULL(PS.Description, MS.ManufacturerSizeDescription) AS ManufacturerSizeDescription
, MS.ManufacturerSizeID
, PS.Quantity AS NewQuantity
, PS.ParentID
, PS.PreOrderQuantity
, MS.Size
, PS.Sku
FROM
Purchasing.VendorPart AS vp WITH(NOLOCK)
JOIN Purchasing.ManufacturerPartToVendorPart AS mptvp WITH(NOLOCK)
ON vp.VendorPartID = mptvp.VendorPartID
JOIN dbo.tblProductSize AS PS WITH(NOLOCK)
ON PS.ProductSizeID = mptvp.ManufacturerPartID
LEFT JOIN Product.ManufacturerSize AS MS WITH(NOLOCK)
ON PS.ManufacturerSizeID = MS.ManufacturerSizeID
WHERE mptvp.VendorPartID = @VendorPartID
END
Or this (Lists > Columns > Format > Place each item on new line > Place comma before each item:
CREATE PROCEDURE [Product].[usp_ManufacturerPart_SEL_ByVendorPartID]
(
@VendorPartID INT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
PS.ProductSizeID AS ManufacturerPartID
, PS.GenderID
, PS.GenericSizeID
, PS.ManufacturerDescription
, PS.ManufacturerID
, ISNULL(PS.Description, MS.ManufacturerSizeDescription) AS ManufacturerSizeDescription
, MS.ManufacturerSizeID
, PS.Quantity AS NewQuantity
, PS.ParentID
, PS.PreOrderQuantity
, MS.Size
, PS.Sku
FROM
Purchasing.VendorPart AS vp WITH(NOLOCK)
JOIN Purchasing.ManufacturerPartToVendorPart AS mptvp WITH(NOLOCK)
ON vp.VendorPartID = mptvp.VendorPartID
JOIN dbo.tblProductSize AS PS WITH(NOLOCK)
ON PS.ProductSizeID = mptvp.ManufacturerPartID
LEFT JOIN Product.ManufacturerSize AS MS WITH(NOLOCK)
ON PS.ManufacturerSizeID = MS.ManufacturerSizeID
WHERE mptvp.VendorPartID = @VendorPartID
END
Basically, I'd like the following:
- First column to be indented 1 tab and subsequent columns to be led by a comma that lines up with the block keyword (SELECT, ORDER BY, SET, INSERT, VALUES, etc.) followed by 1 tab.
- The BEGIN and END of a stored procedure block to not be indented but those of an IF/ELSE block should be indented.
- The WHERE keyword should be always be on its own line.