Red Gate forums :: View topic - Stored procedure parameters not available after 150 lines
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

Stored procedure parameters not available after 150 lines

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
PrivatePyle



Joined: 20 Aug 2014
Posts: 2

PostPosted: Wed Aug 20, 2014 8:35 am    Post subject: Stored procedure parameters not available after 150 lines Reply with quote

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:
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





Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 249
Location: Red Gate Software

PostPosted: Wed Aug 20, 2014 10:02 am    Post subject: Reply with quote

Hi PrivatePyle,

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

%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.
Back to top
View user's profile Send private message
PrivatePyle



Joined: 20 Aug 2014
Posts: 2

PostPosted: Fri Aug 22, 2014 2:46 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 249
Location: Red Gate Software

PostPosted: Fri Aug 22, 2014 3:08 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 249
Location: Red Gate Software

PostPosted: Tue Sep 02, 2014 11:11 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group