Stored procedure parameters not available after 150 lines

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

Stored procedure parameters not available after 150 lines

Postby PrivatePyle » Wed Aug 20, 2014 8:35 am

We are working on rather large stored procedures all the time. Therefore it is crucial for us, to have intellisense suggesting us stored procedure parameters.

We noticed, that SQL Prompt does not suggest us parameters after a few dozen lines inside an create SP script. Is there any setting, that needs to be set to get this working in all of the script, or is it a bug?

We are using SQL Prompt 6.3.0354 on a Windows 7x64 System in SSMS 2008R2 on a SQL Server 2008R2.

Here's an example:


Code: Select all
CREATE PROCEDURE [dbo].[pr_TestProc]
    (
   @fi_Param INT = 0
   )
   
AS
BEGIN

    SELECT 'xxx'
   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT   
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    SET @fi_Param = 5 --INTELLISENSE STILL WORKING
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    SET         --> @fi_Param NOT SUGGESTED ANYMORE
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT


END

GO





PrivatePyle
 
Posts: 2
Joined: Wed Aug 20, 2014 8:20 am

Postby Aaron Law » Wed Aug 20, 2014 10:02 am

Hi PrivatePyle,

You can tweak how far Prompt will look back in the script for suggestions by editing:
Code: Select all
%localappdata%\\Red Gate\\SQL Prompt 6\\RedGate_SqlPrompt_Engine_EngineOptions.xml

And then increasing <ParserLookBackDistance> from 5000 to something larger (adding another 0 on should work).

This was originally put in place for performance reasons and could cause the Prompt suggestions to lag on larger scripts. Our 6.4 beta has a few performance improvements that might help here and we're also currently looking into some ways to remove this limit entirely, but it'll be some time before they're ready for release.

Let me know if this helps.

Thanks,
Aaron.
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby PrivatePyle » Fri Aug 22, 2014 2:46 pm

We are comparing some tools for SQL coding at the moment.

I wasn't able to try your tweak yet, but it seems a bit odd, that you have to keep your limit that low, to have an acceptable performance.

ApexSQL Complete can do it... for free... and it's not really that slow. Or am I missing something?
PrivatePyle
 
Posts: 2
Joined: Wed Aug 20, 2014 8:20 am

Postby Aaron Law » Fri Aug 22, 2014 3:08 pm

Hi PrivatePyle,

Indeed it is very low by default and we'll be increasing this in our 6.4 release within the next month. The current setting is a legacy limitation from a few years ago and so is much less relevant now, but apologies for not increasing it earlier.
Hopefully after trialling both you'll have a better overall experience with Prompt, and please do give the 6.4 beta a try with the look back limit increased.

Let me know if you have any questions.

Thanks,
Aaron.
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby Aaron Law » Tue Sep 02, 2014 11:11 am

A quick update on this: we've removed the ParserLookBackDistance from the latest beta (6.4.0.574) and included a couple extra optimizations. With the latest beta you should always get suggestions for stored procedure parameters, no matter how far back in the script they were declared.
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software


Return to SQL Prompt 6

Who is online

Users browsing this forum: No registered users and 0 guests