1

When coding in an empty query window, IntelliSense gives me useful collapse/expand buttons on each BEGIN-END statement to hide underlying code, apparently called "outlining". However, when I rightclick on a large multistatement tabular function to modify it, I only get these collapse/expand buttons on the alter funtion line and on multi line comments. Is there a way to get outlining also on the BEGIN statements when modifying a function?

below the component versions: SQL Server Management Studio v18.0

  1. SQL Server Management Studio 15.0.18118.0
  2. Microsoft Analysis Services Client Tools 15.0.1300.131
  3. Microsoft Data Access Components (MDAC) 6.1.7601.17514
  4. Microsoft MSXML 3.0 4.0 6.0
  5. Microsoft Internet Explorer 9.11.9600.19399
  6. Microsoft .NET Framework 4.0.30319.42000
  7. Operating System 6.1.7601
jeroenymo
  • 21
  • 3
  • But according to microsoft [If you're working with a really large set of code, that's helpful.](https://blogs.msdn.microsoft.com/buckwoody/2009/07/30/code-collapse-and-expand-feature-in-sql-server-management-studio-2008/). Is this behaviour happening in all large procedures? Do you have a different version of SSMS to test if the same happens? – Ronaldo Jul 31 '19 at 11:25
  • my mistake, ive editted the question, this is about large multiline tabular functions, not procedure. With procedures it works – jeroenymo Jul 31 '19 at 11:29
  • I just noticed the same happens in SSMS v17.9.1 when it comes to **functions**. I also found out if you change the statement from ALTER FUNCTION to ALTER PROCEDURE while you're editing, it enables the outlining. It might be useful for you to work while there is no real solution. Please if you use this aproach, don't forget to change your code back to ALTER FUNCTION before saving the changes. – Ronaldo Jul 31 '19 at 11:54

1 Answers1

1

Ok, so the answer is that there is no real support for outlining WITHIN multistatement tabular functions. A workaround is to comment out some pieces and add a few declare statements while working, and changing it back to execute.

--ALTER function [dbo].[SimulatePremium] ( --<--- comment this

declare                                         --<-- add this

    @Ch nvarchar(50)
,   @ve nvarchar(50)
,   @gu nvarchar(50)
,   @kw int
,   @se int
,   @ag int
,   @vP decimal(19,7)
,   @zi nvarchar(5)
,   @pr nvarchar(100)
,   @sy int
,   @bm int
,   @re int
,   @vM nvarchar(50)
,   @sp int
,   @de int
--) RETURNS                                  --<-- comment this
declare                                      --<-- add this

@ret TABLE (
    vc nvarchar(50)
,   gu nvarchar(50)
,   measure nvarchar(50)
,   value decimal(19,7)
)
--AS                                          --comment this
jeroenymo
  • 21
  • 3