I"m running into the "Invalid length parameter passed come the LEFT or SUBSTRING function" error, but it walk away and the questions works when I encompass the tower I"m passing into those functions, any kind of clue?

Doesn"t work:

SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX("
", Email.Locator) - 1)) as Manager --, Email.LOCATORFROM SALESQUOTATIONTABLE as SQTINNER sign up with HCMWORKER as H top top SQT.WORKERSALESRESPONSIBLE = H.RECIDINNER join DIRPARTYTABLE as D top top H.PERSON = D.RECIDINNER sign up with LOGISTICSELECTRONICADDRESS AS email ON D.PRIMARYCONTACTEMAIL = Email.RECID Invalid length parameter passed come the LEFT or SUBSTRING function

Works:

SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX("
", Email.Locator) - 1)) as Manager , Email.LOCATORFROM SALESQUOTATIONTABLE together SQTINNER join HCMWORKER together H ~ above SQT.WORKERSALESRESPONSIBLE = H.RECIDINNER sign up with DIRPARTYTABLE together D on H.PERSON = D.RECIDINNER join LOGISTICSELECTRONICADDRESS AS email ON D.PRIMARYCONTACTEMAIL = Email.RECIDThis is continuously repeatable, and the only change I made come the questions was had the "Email.LOCATOR" column.This query was functioning for years and just randomly quit working today. I"m pretty particular it"s a data issue, however am quiet perplexed why selecting the Email.LOCATOR tower fixes the issue.

You are watching: Invalid length parameter passed to the left or substring function.


*

*

I think the concern is similar to this one: weird behaviour in TSQL duty (parameter through int change or NULL behaves differently)?

Specifically what Aaron Bertrand mentions in his answer:

... Due to the fact that you can"t constantly rely ~ above SQL Server filtering rows prior to attempting calculations

What i think wake up is that Email.Locator has some values that carry out not save a
. As soon as these values space processed, the CHARINDEX() is 0 and also the LEFT() is called with parameter -1, therefore the error is thrown.

Bu why the error is thrown in one query and not the other? It"s likely due to the fact that the two queries are executed with different plans. The optimizer chooses a different setup (due come the extra pillar or due different statistics 보다 last month or for every little thing reason) and also all the worths of the column are review (and the calculations space done) prior to the join to the other tables.

To prevent the issue, I indicate you use CASE, instead of

LEFT(Email.Locator, CHARINDEX("

*

In every likelihood, the worry is certainly a data issue. A brand-new row has been included to the LOGISTICSELECTRONICADDRESS table, v a LOCATOR that has actually no "
" in it.

Changing the query means that the full value the LOCATOR has to be lugged through come the last results. In that case, there"s no specific advantage to performing the calculation till the final an outcome set has been determined, therefore SQL Server is waiting until the finish of the selection process to calculation the worth of Manager.

See more: This Thing Rips R2 Rig Edition, Thisthingrips R2 Series Rig Vaporizer

Based ~ above the results you"re getting, without LOCATOR in the select list, SQL Server is selecting to compute the value of Manager before deciding what the final an outcome rows are. It"s feasible that worth of Manager will certainly be lot smaller 보다 the full value that LOCATOR, therefore calculating rather of transferring the full LOCATOR value forward would save memory. If the data native LOGISTICSELECTRONICADDRESS is joined right into the result set data prior to some of the other tables room joined in, then the calculation could be carry out on rows the won"t be reverted in the final an outcome set.

You didn"t ask how to resolve this, but (for the sake of completeness), girlfriend should check the value changed by CHARINDEX. If you desire rows whereby LOCATOR has no "
", Email.Locator) - 1)) else "" end AS ManagerFROM SALESQUOTATIONTABLE as SQTINNER sign up with HCMWORKER together H ~ above SQT.WORKERSALESRESPONSIBLE = H.RECIDINNER join DIRPARTYTABLE as D on H.PERSON = D.RECIDINNER join LOGISTICSELECTRONICADDRESS AS email ON D.PRIMARYCONTACTEMAIL = Email.RECID(Of course, you can return the complete LOCATOR value rather of an empty string, that"s her call)

If girlfriend don"t want to watch rows wherein the "
" is missing, you can just examine in the whereby clause:

SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX("
", Email.Locator) - 1)) together ManagerFROM SALESQUOTATIONTABLE as SQTINNER join HCMWORKER as H ~ above SQT.WORKERSALESRESPONSIBLE = H.RECIDINNER sign up with DIRPARTYTABLE as D ~ above H.PERSON = D.RECIDINNER join LOGISTICSELECTRONICADDRESS AS email ON D.PRIMARYCONTACTEMAIL = Email.RECIDWHERE CHARINDEX("