4.0.2.27 Shortcomings

PDinCA

First Issue:
I have a 500-line UDF that returns a table and needed to augment it. SQLPrompt doesn't handle the following at all:

1. No suggestions for adding a WHEN condition to a CASE within the outermost WHERE clause. 2. No suggestions for columns from the main query when adding a correlated subquery under an EXISTS in the following snippet, with the column in question being od.OrderDetailID and the table being OrderDetail, alias od (as this post includes Code, I can't highlight anything within it ). The "main" SELECT mentioned is nine levels of SELECT..FROM (...) deep in the overall SELECT, which may have something to do with it:

FROM dbo.OrderDetail od  WITH ( ROWLOCK )
       INNER JOIN dbo.Orders o  WITH ( ROWLOCK )
               ON o.OrderID = od.OrderID
 WHERE od.OrderStatusID  NOT IN ( dbo.OrderStatus_OnHold()
                                , ISNULL( ( SELECT dbo.OrderStatus_Cancelled()
                                             WHERE od.OrderStatusID = dbo.OrderStatus_Cancelled()
                                               AND od.CancelDate   <= '2008-11-25'
                                            UNION
                                            SELECT dbo.OrderStatus_Cancelled()
                                             WHERE od.OrderStatusID = dbo.OrderStatus_Cancelled()
                                               AND od.CancelDate   >= '2008-11-25'
                                               AND NOT EXISTS ( SELECT 1
                                                                  FROM dbo.OrderDetailSale ods  WITH ( ROWLOCK )
                                                                 WHERE ods.OrderDetailID = od.OrderDetailID
                                                              )
                                          )
                                          , -1
                                        )
                                )

3. The UDF has 5 parameters. None appear in the suggestions.Second Issue:
A simple script declares several variables and executes some SELECTs. The original query is from a file I regularly use and I had run it immediately before pasting. I pasted an additional chunk of code into it and that code contains a SELECT from a UDF. I double-clicked the final UDF parameter and expected at least the declared variables to appear as suggestions - nothing. Variable is @OrderID:

DECLARE @BeginDate datetime
      , @EndDate datetime
      , @IndividualID int
      , @OrganizationID int
      , @OrderID int

SELECT   @BeginDate = '11/25/2008'
       , @EndDate = dbo.udf_GetLastMomentOfDay('7/31/2009')
       , @OrderID = 414760

SELECT ts.OrderID
     , CASE
          WHEN ts.TransactionTypeID = dbo.TransactionType_Cancellation() THEN ts.TransactionAmount * -1
          WHEN ts.TransactionTypeID = dbo.TransactionType_Adjustment()
           AND CAST(memo AS nvarchar(MAX)) = 'refund shipping charge overpayment' THEN 0.00
          ELSE                                         ts.TransactionAmount
       END   AS Amount
  FROM dbo.TransactionSummary(NULL, @EndDate, '1,8,9,3,5,6', NULL, NULL, NULL, @OrderID ) AS ts

Third Issue:
Adding a CTE to top of the big UDF in the First Issue, after typing

WITH CTE_NextDay ( NextDay )
           AS ( SELECT dbo.

I hoped to get a list of scalar functions - snippets were the only suggestions.]
Fourth Issue:
Sadly, it is FROZEN. SSMS is consuming 50% of the CPU and the SQLPrompt suggestion pop-up is showing but unresponsive. I have an ad-hoc script that I left open overnight in ssms and just started to add a line to, as in, New Line, typed ", COUNT(" and the suggestion box appeared, but I kept on typing as I knew I just wanted "*) AS Shipments" then hit Ctrl+C at which point I noticed I was dead in the water. It has been ten minutes and I guess I'll just have to KILL ssms :shock:. I have snagged the screen via Snag-it so I can send it and the script if you would like... Now that I've restarted ssms, the add-on cannot be loaded! REBOOT!
REBOOT was useless - uninstal is the only way I can see that ssms will load the add-in. BEWARE: avoid killing ssms if at all possible - you may end of with a corrupt environment like me...

cairnz

Thought I'd just add to this thread instead of make a new one.

When creating a stored procedure and wanting to call a second procedure in the first one, writing EXEC <partially_name_of_procedure> and hit enter when the dropdown list is on the correct one, you'll get the whole create statement for the SP. This should only appear if the name of the SP is prefixed by ALTER PROCEDURE in my opinion. It's a hassle to remember to completely type the SP name or cut away all the statements.

When creating / altering a view, SQL Prompt will not format the query properly.

Formatted:
SELECT
    name ,
    object_id ,
    principal_id ,
    schema_id ,
    parent_object_id
FROM
    sys.tables

Using view:

Formatted:
CREATE VIEW bob AS 
SELECT
name ,
object_id ,
principal_id ,
schema_id ,
parent_object_id
FROM
sys.tables

In other news, the speed increase is very much noticed!