October 13, 2014 at 3:21 am
Hi Folks,
While executing the below function iam getting issue , can anyone please help me out to resolve this, as is this of high priority and blocking the UI.and the issue is Invalid length parameter passed to the LEFT or SUBSTRING function.[/u]
ALTER FUNCTION [dbo].[SCH_RoleList]
(
@User varchar(20),
@Application varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN
-- Declare the return variable here
DECLARE @List varchar(2000)
SET @List = ''
SELECT @List = @List + CAST(r.RoleName As varchar(1000)) + N', '
FROM dbo.aspnet_Users AS u
INNER JOIN dbo.aspnet_Applications AS a ON u.ApplicationId = a.ApplicationId
INNER JOIN dbo.aspnet_Roles AS r ON a.ApplicationId = r.ApplicationId
INNER JOIN dbo.aspnet_UsersInRoles AS ur ON u.UserId = ur.UserId
AND r.RoleId = ur.RoleId
WHERE (a.ApplicationName = @Application) and
AND (SUBSTRING(u.UserName, CHARINDEX('\', u.UserName) + 1, 20)) = (SUBSTRING(@User , CHARINDEX('\', @User ) + 1, 20))
ORDER BY r.RoleName
SET @List = SUBSTRING(@List, 1, Len(@List) - 1)
RETURN @List
October 13, 2014 at 3:25 am
That happens when the length parameter is <0.
Probably coming from this line
SET @List = SUBSTRING(@List, 1, Len(@List) - 1)
If the list is empty, that'll throw the error you mention.
p.s. You might want to rather use the FOR XML PATH method to concatenate strings. I seem to recall the method you're using can be inconsistent about what it returns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2014 at 3:29 am
HI ,
Thanks for your quick reply can you please provide some solution since iam new to sql server
Thanks,
Leo
October 13, 2014 at 3:32 am
Well, the error only occurs if @List is empty, so what do you think the solution is?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply