What is Displayed in the Candidate List

• 
  sp_executesql does not show up in the candidate list.

  amrinders87 September 01, 2006 13:17

  - 0 +
• 
  I posted this as a seperate message, but I guess it should go in this thread:
  

  ---

  
  Candidate List in FROM clause:
  
  
  In the WHERE clause, the candidate list displays fields from tables that are not in the FROM clause.
  
  I do not like how it shows these fields as options or how it adds the table to the FROM clause w/o a join. It is like if Intellisense displayed variables that are out of scope.
  
  How often do you have a multi-table FROM clause where a table is not part of a join? Even if Sql Prompt adds the table for you, you still need to go 'take care of it' by making the automatically added table part of a join.
  
  It is good that they are listed first, but it makes that list long and harder to look at. It also gives you the ability to automatically generate 'bad' Sql (the table w/o the join). I think just showing the fields from the current tables is best.

  rjpaulsen September 01, 2006 17:59

  - 0 +
• 
  When looking a stored procedure in candidate list on right side window show definition (code) of selected stored procedure. A wery nice options.
  
  But I think that this options will be more usefull if you add code select and copy to clipboard. Just to look the code is not enough, in some cases I need some part of code and Select and Copy from definitions window will be welcome.
  
  Uros

  Urosd September 02, 2006 12:43

  - 0 +
• 

  rjpaulsen wrote:

  In the WHERE clause, the candidate list displays fields from tables that are not in the FROM clause.
  
  I do not like how it shows these fields as options or how it adds the table to the FROM clause w/o a join. It is like if Intellisense displayed variables that are out of scope.
  
  How often do you have a multi-table FROM clause where a table is not part of a join? Even if Sql Prompt adds the table for you, you still need to go 'take care of it' by making the automatically added table part of a join.
  
  It is good that they are listed first, but it makes that list long and harder to look at. It also gives you the ability to automatically generate 'bad' Sql (the table w/o the join). I think just showing the fields from the current tables is best.

  
  
  I think you may some fair points here. We probably should prune the columns displayed in the WHERE clause in some way if possible.
  
  We are aware of the FROM issue, and that actually in many cases what you really want to do is a JOIN after the first table has been added, although of course you can write an inner join as follows (though this syntax is hardly best practice):
  
  SELECT t1.c1, t1.c2, ... , t1.cn, t2.c1, t2.c2, ... , t2.cn
  FROM Table1 as t1, Table2 as t2
  WHERE t1.id = t2.t1id
  
  (Without the WHERE clause you'd end up with a cross join.)
  
  However, you have to be careful. What we can't do is make the behaviour an option that you'd set in the options dialog, as under a significant but probably annoyingly large number of circumstances it would do the wrong thing - e.g. would you really *always* want it to insert an INNER JOIN? If not you'd still have to fix the join clause yourself.
  
  What we were thinking of therefore is allowing the insertion behaviour to depend on a modifier key, something like this perhaps (for these examples assume a FROM clause is present with one table, e.g. FROM Table1 as t1):
  
  - User hits ENTER -> INNER JOIN inserted
  - User hits completion key other than enter (e.g. TAB, SPACE) -> table is simply added to list in FROM clause (i.e. cross join)
  - User hits CTRL + ENTER -> LEFT OUTER JOIN inserted
  - User hits SHIFT + ENTER -> RIGHT OUTER JOIN inserted
  - User hits CTRL + SHIFT + ENTER -> FULL JOIN inserted
  
  How do you feel about that? Or would you prefer to see it working a different way?
  
  
  Thanks,

  Bart Read September 04, 2006 09:59

  - 0 +
• 

  Urosd wrote:

  When looking a stored procedure in candidate list on right side window show definition (code) of selected stored procedure. A wery nice options.
  
  But I think that this options will be more usefull if you add code select and copy to clipboard. Just to look the code is not enough, in some cases I need some part of code and Select and Copy from definitions window will be welcome.
  
  Uros

  
  Thanks for this suggestion, we'll try to get it in for the final release.
  Cheers,
  Bart

  Bart Read September 04, 2006 09:59

  - 0 +
• 
  I have found that even though I have a SELECT FROM <table> structure, example:
  
  SELECT
  FROM [dbo].[C21_TB_TimeRecord]
  
  SQL Prompt displays all candidate fields from all tables, *unless* I make the next part of the SELECT specifically name the table:
  
  SELECT [dbo].[C21_TB_TimeRecord].[tr_Case],
  FROM [dbo].[C21_TB_TimeRecord]
  
  Once this is done, then the candidate list is now restricted to fields from the required table, and once I figured out to turn off "Qualify non-aliased columns", I can get what I am trying to get - like this:
  
  SELECT [dbo].[C21_TB_TimeRecord].[tr_Case], [TR_Case_CaseId]
  FROM [dbo].[C21_TB_TimeRecord]
  
  I can then go back and remove the the table name qualifier from the first candidate and SQL Prompt continues to work as required.
  
  Is this "by design", is there some way to turn it off?
  
  Thanks
  Chris

  Chris Palmer September 04, 2006 19:49

  - 0 +
• 
  Hi Chris,
  
  
  No, I think that's a bug. SELECT FROM is tricky because it's completely invalid SQL. It causes our parser to lose its lunch completely so I have to fudge a valid SQL statement in order to get an AST. I thought I'd fixed this so it would display columns from the tables/views in the FROM clause, followed by all other columns (because you might want to add more tables), but I guess it's not working as well as I thought.
  
  
  Thanks,
  Bart

  Bart Read September 05, 2006 07:45

  - 0 +
• 
  While adding snippets it would be good to have a short description.
  Like "Alter cube dimension" instead of acud in the candidate list.
  "Alter cube dimension member" instead of acum in the candidate list.

  sangha September 05, 2006 19:04

  - 0 +
• 
  Hi there, we're thinking of adding this actually. The description field will be available in the configuration panel, but as I say, we may also add it to the candidate list.
  Thanks,
  Bart

  Bart Read September 06, 2006 04:32

  - 0 +
• 
  The onwer should appear.
  
  In the WHERE clause, the candidate list displays fields from tables that are not in the FROM clause.

  EsioN September 06, 2006 11:06

  - 0 +
• 
  I just noticed that locally declared variables doesn't appear and when writing stored procedures this would be very handy.

  Lumbago September 08, 2006 08:36

  - 0 +
• 
  That's right. Unfortunately we didn't have time to get that support into the beta. It will be there in the final release though.
  Thanks,
  Bart

  Bart Read September 08, 2006 08:38

  - 0 +
• 
  Do you have an estimated time of release for the final version?

  Lumbago September 08, 2006 08:41

  - 0 +

Add comment

Please sign in to leave a comment.