SQL Prompt and case statements

blitzd

I'm finding that SQL prompt stops working if I am adding a column before a case statement, for instance the following SQL code:

CREATE TABLE [dbo].[TableTest](
	[ColumnOne] [int] NULL,
	[ColumnTwo] [varchar](10) NULL,
	[ColumnThree] [bit] NULL,
	[ColumnFour] [int] NULL,
	[ColumnFive] [int] NULL,
	[ColumnSix] [varchar](100) NULL,
	[ColumnSeven] [varchar](100) NULL,
	[ColumnEight] [varchar](100) NULL,
	[ColumnNine] [varchar](100) NULL,
	[ColumnTen] [varchar](100) NULL
)

SELECT  ColumnOne ,
        ColumnTwo ,
        ColumnThree ,
        CASE WHEN TT.ColumnFour = 1 THEN 'One'
             ELSE 'Dunno'
        END AS ColumnFourText ,
        ColumnFour ,
        ColumnFive ,
        ColumnSix ,
        ColumnSeven ,
        ColumnEight ,
        ColumnNine ,
        ColumnTen
FROM    dbo.TableTest TT

If I start typing on the line before the case statement I do not get ColumnOne to ColumnTen as options. I get the TT alias as an option, but if I use it (type TT. ) then I do not get any columns as options. If I start typing after the case statement I do get these columns all as options, and if I use the TT alias I also see all of the columns as options.

Essentially, the case statement seems to break the suggestions that SQL Prompt provides.

Eddie D

Thank you for your forum post.

Can you please confirm if you have enable this option enabled?

Open SSMS ->SQL Prompt 5 menu ->Options -> Under suggestions -> Types of suggestion -> Enable the check box "List all database columns after a SELECT statement".

Hopefully having the above option enabled will resolve this problem.

Many Thanks
Eddie

blitzd

eddie davis wrote:

Thank you for your forum post.

Can you please confirm if you have enable this option enabled?

Open SSMS ->SQL Prompt 5 menu ->Options -> Under suggestions -> Types of suggestion -> Enable the check box "List all database columns after a SELECT statement".

Hopefully having the above option enabled will resolve this problem.

Many Thanks
Eddie

That does appear to bring in the columns, however it appears to bring in every other database column as well - and I think that may be worse for scenarios where the suggestions were already working.

I have noticed that when I add a comma first on that line, hit home and start typing before it that the suggestions are now working properly again (without the List all database columns after a SELECT statement' option checked). This may work for the time being, but it would be nice if I didn't have to do that.

Eddie D

Thank you for your reply.

That does appear to bring in the columns, however it appears to bring in every other database column as well - and I think that may be worse for scenarios where the suggestions were already working.

At this stage you have only typed SELECT, how is the product to know that you only want columns from a specific table.

I have noticed that when I add a comma first on that line, hit home and start typing before it that the suggestions are now working properly again (without the List all database columns after a SELECT statement' option checked). This may work for the time being, but it would be nice if I didn't have to do that.

Cannot reproduce the behaviour you have described above. I am currently running V5.1.9.4. What version of SQL Prompt are you using?


Many Thanks
Eddie

blitzd

Eddie,

I think you have misunderstood my problem. I'm editing an existing statement, not typing it anew and expecting SQL Prompt to magically determine what table I'm referencing - although that would be kind of cool, and maybe a cup of coffee to boot?

So anyways, here's the scenario again:

SELECT  ColumnOne , 
        ColumnTwo , 
        ColumnThree , 
--1
        CASE WHEN TT.ColumnFour = 1 THEN 'One' 
             ELSE 'Dunno' 
        END AS ColumnFourText , 
--2
        ColumnFour , 
        ColumnFive , 
        ColumnSix , 
        ColumnSeven , 
        ColumnEight , 
        ColumnNine , 
        ColumnTen 
FROM    dbo.TableTest TT 

If I start *editing* this statement, and typing directly on the line where the --1 comment is, I can see the TT alias in the suggestions, but none of the columns, and upon selecting the TT alias I get no suggestions for columns there either. If I do this same thing where the --2 comment is, I get suggestions just fine, and I also specifically see the column names in the suggestion list without typing the alias as well. I also get suggestions at --1 if I first put a comma on the line, and then put the cursor in front of it and start typing.

My version of SQL Prompt is 5.1.8.2.

I can't try the latest and greatest version at the moment, as after I installed it SQL Prompt would no longer start on my testing machine any more (says it requires a repair of SSMS and VS, oh joy!).

blitzd

Repair went quicker than expected... 5.1.94.2 shows the same behaviour.

MikeyC

I've noticed the same behavior, though I am not sure it is limited to CASE. I'm pretty sure a number of us have reported it before, and have always been told to put the comma in first, which does work, but is less than ideal.

Eddie D

I have submitted a bug report for the SQL Prompt Development Team to consider. The reference for this report is SP-4188.

Sorry for the confusion on how to reproduce the reported problem. You will be please to know I can now reproduce the problem.

Also a support call has been created, call reference F0053297.

Many Thanks
Eddie