Code formatting to allow for SSMS parameters

fatherjack2

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.

Chris Auckland

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.

fatherjack2

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

Chris Auckland

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.

fatherjack2

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?

Chris Auckland

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.