What are the challenges you face when working across database platforms? Take the survey
Options

Code formatting to allow for SSMS parameters

fatherjack2fatherjack2 Posts: 311
hi,

would it be possible to have the inclusion of SSMS parameters in the code formatting please? Currently the layout process returns an "errors were encountered" message.

Senior DBA
Careers South West Ltd

Comments

  • Options
    Hi Fatherjack,

    Is there any chance you could post a short script that reproduces the error?

    I just want to make sure I don't missunderstand what you're looking for.
    Chris
  • Options
    Hi Chris,

    any of the SSMS templates seem to do it as well as any that I put in scripts myself. The shortest one I can think of is the Create Database one:
    -- =============================================
    -- Create database template
    -- =============================================
    USE master
    GO

    -- Drop the database if it already exists
    IF EXISTS (
    SELECT name
    FROM sys.databases
    WHERE name = N'<Database_Name, sysname, Database_Name>'
    )
    DROP DATABASE <Database_Name, sysname, Database_Name>
    GO

    CREATE DATABASE <Database_Name, sysname, Database_Name>
    GO

    in order to use it you press Alt +Q, S and the parameter completion dialog comes up to accept parameter values but if you try to apply layout from Refactor or Prompt it crashes with an error mesage of:
    "SQL Prompt was unable to format this scrupt because syntax errors were found while it was being parsed. These errors have been highlighted."

    Hope this helps, any problems feel free to call.

    Jonathan

    Senior DBA
    Careers South West Ltd
  • Options
    Hi Jonathan,

    Thanks for the clarification.

    With the above example, the problem is that the SQL itself is not valid. The <Database_Name, sysname, Database_Name> statements cause parsing errors in SQL Prompt, Refactor and the SSMS parser.

    Once you modify the statement to state the database name, the SQL should parse and you will be able to lay-out the code.

    I hope this helps.
    Chris
  • Options
    Chris, that is my point. I understand its not valid SQL but I want to save this script, with proper layout, to reuse it. Is it not possible for Prompt and Refactor to ignore the parameter(s) ie anything between < and > but still layout the code?

    Senior DBA
    Careers South West Ltd
  • Options
    Hi Jonathan,

    I can't find a workaround for this, so I've logged it as a feature request for a future version.

    For your reference, our internal tracking code for the feature is SP-2964.

    Thanks for the suggestion.
    Chris
Sign In or Register to comment.