SQL Assistant questions

Adding the:

	Code:
	WITH ...

Does seem to keep it left justified...

But now my parens don't line up.

	Code:
	WITH CTETBL AS ( SELECT FOO FROM BAR WHERE X = 'Y' )

I'd prefer

	Code:
	WITH CTETBL AS ( SELECT FOO FROM BAR WHERE X = 'Y' )

Or something similar to how the formatting WAS justifying it when you were on Chr 1 in the editor.

OK changing the specific WITH formatting logic to this:

	Code:
	WITH ... AS ( ..., ... AND ... OR ... )

Seems to override the (..) formatting logic.

Strange. I've no apps to create videos and no db accessible at this time but nothing like that happens here. Though I doubt any of the formatting rules are left in their original state, plus I use leading commas instead of trailing ones. Try playing with those rules, you might sort it out...

EDIT: also, keep in mind that it is suggested to start cte expressions with an ; to make sure that WITH keyword does not interfere with the previous statement.

gemisigo wrote:

Strange. I've no apps to create videos and no db accessible at this time but nothing like that happens here. Though I doubt any of the formatting rules are left in their original state, plus I use leading commas instead of trailing ones. Try playing with those rules, you might sort it out...

EDIT: also, keep in mind that it is suggested to start cte expressions with an ; to make sure that WITH keyword does not interfere with the previous statement.

I copied the formatting rules to a new set before I modified them so I'd always have a way to refer back to the originals.

Even with the original rule profile set as default formatting that WITH (or maybe anything in parens?) gets screwy in SSMS2008 (haven't tested it in anything else extensively but I imagine it'll be similar. Of course adding the WITH formatting you mentioned or my extended WITH formatting that no longer happens.

I am aware of the semicolon before WITH rule, but for this case the WITH is the top most line in the editor box so I just did not enter it.

Just in case...
There is an easy option to duplicate any existing style or rules, use the right-click menu in options or little copy icons at the top.
You can enable / disable rules by ticking their check box. So if you have several alternative rules, you can enable them as needed without deleting the other ones.
Shall you want to revert back to factory default settings, use the Import/Export button in the Options, then select to Import default settings.

Yes I copied the rules with the copy icon.

I did reset the rules last night to test I be sure I hadn't broken one with the CTE logic. After I tested the default tsql rule I saw that my custom one was gone (I guess deleted on import of defaults?)

We're you able to reproduce the formatting weirdness shown in my screencast on the prior page?

I don't see a screenshot attached. Would you please attach the image again?

It was a screencast. There is a URL in one of my posts back a page. I'd post it again but I'm on my phone right now.

It seems that if you keep the (header) formatting enabled or any comment (or maybe any code) above the CTE, this problem with WITH doesn't exist.. the with doesn't jump to the right if there's a header involved.

http://screencast.com/t/ZyS8LbkDLC