CODEWITHSUNDEEP
sqlsql-serverreporting-servicessubstringssrs-2012
jackstraw22
jackstraw22

Reputation: 641

Invalid length parameter error for stored procedure

I'm passing a list of names from SSRS to a SQL Server stored procedure but I'm getting an error:

Invalid length parameter passed to the LEFT or SUBSTRING function

This is my code:

Select substring(item, 1, LEN(item) - 36) 
From dbo.fnsplit(@manager, ',')

The reason for the substring is to remove a 36 character GUID that is attached to the end of the manager name. The names are passed like this:

    John Smith, Tom Perry

I've read the error could be caused by spaces, but I can't figure out how to fix this.

Upvotes: 0

Views: 405

Answers (2)

shA.t
shA.t

Reputation: 16958

If you really want to remove last 36 characters from right side in all cases!? So, I think this hack-version should work ;).

select 
    replace(item, right(item, 36), '')
from 
    dbo.fnsplit(@manager, ',');

Note: Actually I take 36 characters from right side of text then remove it by replacing with '' ;).

[SQL Fiddle Demo]

But I think your issue should be something like this:

select 
    left(item, abs(len(item + ',') - 37))
from
    dbo.fnsplit(@manager, ',');

[SQL Fiddle Demo]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Just use case:

Select (CASE WHEN len(item) <= 36 THEN item
             ELSE LEFT(item, LEN(item)-36)
        END)
from dbo.fnsplit(@manager, ',')

Upvotes: 2

Related Questions