Confirmed bug in formatting

buckley

Hi Aaron,
Here is a quick report for a bug. I have no time at the moment to isolate it further, hope you can reproduce

When I format the statement below the
ORDER BY [Exists] ,
Gets transformed to
ORDER BY [] ,

Have a good day, Tom

ALTER FUNCTION [Simple].[uf_CompareEnvironment_Permissions]
(
@left VARCHAR(5) ,
@left2 VARCHAR(5) ,
@right VARCHAR(5) ,
@right2 VARCHAR(5) ,
@includeIdentical BIT
)
RETURNS TABLE
AS

RETURN
(
WITH PathDepthOf1
AS ( SELECT dbo.ufStripEnvironment(MemberName) MemberNameStripped ,
dbo.ufStripEnvironment(GroupName) GroupNameStripped ,
MemberName MemberName ,
GroupName GroupName ,
MemberType ,
PathDepth
FROM Simple.vwIsMemberOf
WHERE PathDepth = 1
)
SELECT
-- Top Trick so we can Ordery By in a view/function
TOP 214748364
--myleft.PathDepth,
CASE WHEN myLeft.MemberName IS NOT NULL
AND myRight.MemberName IS NOT NULL THEN '3 Identical'
ELSE CASE WHEN myLeft.MemberName IS NULL THEN '2 OnlyRight'
ELSE '1 OnlyLeft'
END
END [Exists] ,
ISNULL(myLeft.MemberNameStripped, myRight.MemberNameStripped) MemberNameStripped ,
myLeft.MemberName LeftMemberName ,
myLeft.GroupName LeftGroupName ,
myRight.MemberName RightMemberName ,
myRight.GroupName RightGroupName ,
myLeft.MemberType LeftMemberType ,
myRight.MemberType RightMemberType ,
myLeft.MemberNameStripped LeftMemberNameStripped ,
myRight.MemberNameStripped RightMemberNameStripped ,
myLeft.GroupNameStripped LeftGroupNameStripped ,
myRight.GroupNameStripped RightGroupNameStripped
FROM ( SELECT *
FROM PathDepthOf1
WHERE MemberName LIKE '%' + @left + '%'
OR MemberName LIKE '%' + @left2 + '%'
) myLeft
FULL OUTER JOIN ( SELECT *
FROM PathDepthOf1
WHERE MemberName LIKE '%' + @right + '%'
OR MemberName LIKE '%' + @right2 + '%'
) myRight ON myLeft.MemberNameStripped = myRight.MemberNameStripped
AND myLeft.GroupNameStripped = myRight.GroupNameStripped
AND myLeft.MemberType = myRight.MemberType
WHERE myLeft.MemberName IS NULL
OR myRight.MemberName IS NULL
OR @includeIdentical = 1
ORDER BY [Exists] ,
--PathDepth,
LeftMemberName ,
LeftGroupName ,
RightMemberName ,
RightGroupName
)

Aaron L

Hi Tom,

Just to check, which version/build of Prompt are you currently using?

Thanks,
Aaron.

buckley

Hey Aaron, it's 6.5.0.183

Aaron L

Thanks Tom!

I can recreate this in 6.5.0.183, but not in the latest build (6.5.0.330) so it looks like it's already been fixed. You can grab the latest from SQL Prompt->Help->Check for Updates inside SSMS or from our website.

I'm pretty sure it's been fixed, but if you still have problems after upgrading just let me know!

Thanks,
Aaron.

buckley

Good to know Aaron. I can't immediately upgrade but this is a little bug anyways. Loving prompt !

buckley

FYI

I was thinking that formatting had a prob with sql keywords like Exists

But I'm guessing that its if you use brackets in the order by

ORDER BY [Last Time Job Ran On] DESC
=>
ORDER BY [] DESC

This is just FYI as its fixed in a later build as you cannot reproduce

Aaron L

Hi Tom,

Thanks for the extra info - I had a bit more of a dig and it looks like this was a knock on from the work we did during the beta for expanding aliased column expressions on some statements. Some of the variables weren't setup correctly in certain cases so it expanded to nothing. This should have all been fixed as of the stable release however.

Glad to hear you're still enjoying Prompt!

Aaron.