sp_BlitzCache: Invalid length parameter passed to the LEFT or SUBSTRING function when compile_time_value contains mismatched open/close parenthesis.

[PeteSral]

Version of the script
@Version = '8.02', @VersionDate = '20210322';

What is the current behavior?
Query completed with errors. Error in the messages:

Updating procs
Msg 537, Level 16, State 3, Procedure sp_BlitzCache, Line 3735 [Batch Start Line 0]
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Updating SET options

If the current behavior is a bug, please provide the steps to reproduce.
exec sp_blitzcache

Code Snippet

RAISERROR(N'Updating procs', 0, 1) WITH NOWAIT;
UPDATE s
SET    s.variable_datatype = CASE WHEN s.variable_datatype LIKE '%(%)%' 
                                  THEN LEFT(s.variable_datatype, CHARINDEX('(', s.variable_datatype) - 1)
								  ELSE s.variable_datatype
                             END,
       s.converted_to = CASE WHEN s.converted_to LIKE '%(%)%' 
                             THEN LEFT(s.converted_to, CHARINDEX('(', s.converted_to) - 1)
                             ELSE s.converted_to
                        END,
	   s.compile_time_value = CASE WHEN s.compile_time_value LIKE '%(%)%' 
                                   THEN SUBSTRING(s.compile_time_value,  CHARINDEX('(', s.compile_time_value) + 1,  CHARINDEX(')', s.compile_time_value) - 1 - CHARINDEX('(', s.compile_time_value) )
				WHEN variable_datatype NOT IN ('bit', 'tinyint', 'smallint', 'int', 'bigint') 
									AND s.variable_datatype NOT LIKE '%binary%' 
									AND s.compile_time_value NOT LIKE 'N''%'''
									AND s.compile_time_value NOT LIKE '''%''' 
									AND s.compile_time_value <> s.column_name
                                    AND s.compile_time_value <> '**idk_man**'
                                    THEN QUOTENAME(compile_time_value, '''')
									ELSE s.compile_time_value 
							  END
FROM   #stored_proc_info AS s
OPTION (RECOMPILE);
RAISERROR(N'Updating SET options', 0, 1) WITH NOWAIT;

One of the values in the column compile_time_value contains a closing paren ahead of open paren appears to be tripping up the parsing. 1) (something)

Test

DECLARE @compile_time_value NVARCHAR(258) = N'1) (something)';

SELECT CHARINDEX('(', @compile_time_value) + 1 AS [SubString starting_position]
	,CHARINDEX(')', @compile_time_value) - 1 - CHARINDEX('(', @compile_time_value) AS [SubString length];

Result

SubString starting_position	SubString length
5	-3

What is the expected behavior?
Query executed successfully.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) 
	Oct 31 2020 02:54:45 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

[PeteSral]

Note to self: setting the starting_position of the ( found when retrieving the closing ) will keep us out of the negative sub-string length.

DECLARE @compile_time_value NVARCHAR(258) = N'1) (something)';

SELECT CHARINDEX('(', @compile_time_value) + 1 AS [SubString starting_position]
	,CHARINDEX(')', @compile_time_value, CHARINDEX('(', @compile_time_value) + 1) - 1 - CHARINDEX('(', @compile_time_value) AS [SubString length]
    ,SUBSTRING(@compile_time_value, CHARINDEX('(', @compile_time_value) + 1, CHARINDEX(')', @compile_time_value, CHARINDEX('(', @compile_time_value) + 1) - 1 - CHARINDEX('(', @compile_time_value)) As compile_time_value;

SubString starting_position	SubString length	compile_time_value
5	9	something

If you have an open paren followed by an open paren:
Set @compile_time_value = N'(1 (something)';

SubString starting_position	SubString length	compile_time_value
2	12	1 (something

I'll put this into a pull request.

[BrentOzar]

Thanks for the pull request! Merging into the dev branch, will be in the April release with credit to you in the release notes.