Stored procedure parameters not available after 150 lines

PrivatePyle · August 20, 2014 3:35AM

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:

CREATE PROCEDURE &#91;dbo&#93;.&#91;pr_TestProc&#93;
    &#40;
	@fi_Param INT = 0
	&#41;
	
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			--&gt; @fi_Param NOT SUGGESTED ANYMORE
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT
    -- RANDOM TEXT


END

GO

Aaron L · August 20, 2014 5:02AM

Hi PrivatePyle,

You can tweak how far Prompt will look back in the script for suggestions by editing:

%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.

PrivatePyle · August 22, 2014 9:46AM

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?

Aaron L · August 22, 2014 10:08AM

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 L · September 2, 2014 6:11AM

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.

Stored procedure parameters not available after 150 lines

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk