6.4.0.641 - Missing @ from Param kills prompting

PDinCA

If you define a simple SP:

ALTER PROCEDURE users.ModuleUserCreate
    ( @ErrorCode              int            OUTPUT
    , @ErrorMsg               nvarchar(4000) OUTPUT
    , @Rows                   int            OUTPUT
    , @UserID                 int
    , @ModuleID               int
    , @ModuleUserID           int
    , @ActiveDate             date
    , @TerminationTS          datetime       = NULL
    , @CreatedByUserName      nvarchar(254)  = NULL
    )

but when you were typing you missed the @ from one of them:

ALTER PROCEDURE users.ModuleUserCreate
    ( @ErrorCode              int            OUTPUT
    , @ErrorMsg               nvarchar(4000) OUTPUT
    , @Rows                   int            OUTPUT
    , @UserID                 int
    , @ModuleID               int
    , ModuleUserID            int
    , @ActiveDate             date
    , @TerminationTS          datetime       = NULL
    , @CreatedByUserName      nvarchar(254)  = NULL
    )

SQL Prompt takes a break, but doesn't tell you, and no Ctrl+Space will provoke it to do anything for you other than list snippets.

Not very helpful!

Any chance you could save us the aggro with a "Doh!" dialog box, please?

Aaron L

Hi Stephen,

I can recreate this here and will look into making Prompt a little more robust if you do make a typo here (so at least it'll continue to offer suggestions for subsequent parameters)

I'm not sure if a dialog box is the right choice here though, I could imagine it'd could become annoying for some users, especially if they already know the script is invalid and we popup a dialog automatically. If you want to check the query is valid you can use the SSMS command "Parse" (Ctrl+F5) which'll point out any parsing errors in the script:


Do you think that'd work for you?

Thanks,
Aaron.

nhustak

After some fooling around with the latest version I have found the intellisense to be much less 'intelligent'. I mean that it requires the preceding code to be 100% complete or it suggest nothing.

It wasn't this way before. Here's an example I ran into that convinced me of it. I regularly do the return type last. My lack of a return definition is keeping it from deducing the declare statement.

CREATE FUNCTION dbo.fn_get_active_game_guid
(
	@user_guid UNIQUEIDENTIFIER
)
RETURNS <Function_Data_Type, ,int>  <---- the lack of a definition here prevents the code below from popping the intellisense
AS
BEGIN
DECLARE @instance_guid    <--- I'm right here trying to get 'UNIQUEIDENTIFIER' from intellisense. 

	{some valid sql code}

END

Aaron L

Hi Nick,

I just gave it a go in previous versions of Prompt (6.3 and 5.3) and unfortunately it's also broken in them, so it's not something that's just stopped working in the latest build. Typically Prompt is a bit more intelligent when offering suggestions around errors - especially around select statements but it looks like it needs some improvement when encountering them in the headers of procedures and functions.

Thanks for pointing these out, I'll see if we can get a fix in for them soon!

Aaron.

Aaron L

Hi Guys,

I've improved the error recovery around the cases you've pointed out in this private build. We'll have a review of the way we handle errors in other statements next week and see if there's any more places that it could be handled more intelligently (I know how annoying it is to lose Prompt's suggestions!)

Thanks again for pointing these out!

PDinCA

Aaron Law wrote:

...I can recreate this here and will look into making Prompt a little more robust if you do make a typo here (so at least it'll continue to offer suggestions for subsequent parameters)
... If you want to check the query is valid you can use the SSMS command "Parse" (Ctrl+F5) which'll point out any parsing errors in the script... Do you think that'd work for you?...

Don't wish to take offense after 9 good years with Red Gate's tools, but really, "...Ctrl+F5...Do you think that'd work for you?" Of course it darn well will! I suppose I made the mistake of looking at the tool itself (SQL Prompt) to see why IT wasn't doing what it should any more, believing that I shouldn't be relied upon to write character-perfect T-SQL just to keep it satisfied! Silly me - "note to self: if SQL Prompt stops working, press Ctrl+F5 - got it?"

Thankfully, RG hands have been raised and "we should do better" acknowledged. Correct. SQL Prompt shouldn't just up and stop working when a syntax error happens to be present.

Hope the "more tolerant" build comes to RTM soon - don't have time these days to disrupt normal activities with beta releases like the old days.

Aaron L

Sorry Stephen, I didn't mean to cause offence! I completely agree, the correct thing to do here is for Prompt to recover from the error and continue to offer suggestions. This has been fixed in the private build I posted and will be included in a stable release next week.

I should have made this clearer but I only meant to suggest Ctrl+F5 as an alternative workaround for a "Doh!" dialog box, not as an alternative for recovering from the errors.

This particular bug looks like it's been in Prompt for the last 5 years at least so I'm really grateful that you did take the time to report it.

PDinCA

Many thanks Aaron with MY apologies for a testy reply - tough week with nearly all the family sick and my 2nd round since flying back from the UK on the 7th.

Very glad to see you tracked the cause down so quickly and already have a fix.

Look forward to the next release.

Regards

Stephen

Aaron L

Hi Stephen,

Please don't worry about it! Best of luck to you and your family.

I'll let you know when a stable build with these improvements is out.

Thanks,
Aaron.

PDinCA

Hello again, Aaron.

Please add another "intolerant of minor omission" issue. If you look at the code below, I was part-way through the INNER JOIN table name when all I could get from the prompt list was a list of snippets.

INSERT INTO @SYSTEM
     ( SystemID
     , SystemName
     , RowNum
     )
SELECT q.SystemID
  FROM ( SELECT t.SystemID
           FROM @PER_TAG t
          GROUP BY t.SystemID
       )
       INNER  JOIN dbo.t_sys

All I had missed was the alias on the nested query.

Thanks.

Aaron L

Hi Stephen,

We've been looking into this case and we can recover and continue to offer column suggestions for the join clause; unfortunately without having the subquery aliased we wouldn't be able to suggest entirely valid join conditions as the columns wouldn't have an owner name to use for qualifying with.

I'm not sure there's an easy way around not having an alias, we'll have to have a bit more of a think about what the correct thing to do is. Perhaps continuing to offer (potentially incorrect) suggestions isn't the best approach for this one.

Thanks,
Aaron.

PDinCA

Sorry I wasn't clearer in my description- the table name I was wanting suggested wasn't t_sys but what my predecessor named t_system_ref. I showed the code at the point where I was stymied on suggestions and the reason for it being the missed alias. As soon as I typed the alias, the table name came up with Ctrl+Space.

Hope this clarifies.

Aaron L

We've just released 6.4.0.657 which should have fixes for the 3 cases in this thread along with a few others.

If you discover any other places where we could improve this further, please do let us know.

Thanks,
Aaron.