ASP.NET/SQL Server: Conversion failed when converting from a character string to uniqueidentifier

Question

I am using a paramterized query for this. I am passing a comma-delimited string of unique identifiers to this query's parameters which are split. It works fine using a couple of unique identifiers but I think I am hitting a max value around 2000 characters.

I have around 150 areaID's and selecting a few the query works fine. Selecting all of the areas the query fails and returns

Conversion failed when converting from a character string to uniqueidentifier.

Any suggestions for getting more values into query without running into this issue?

Area.AreaID IN (SELECT CONVERT(UNIQUEIDENTIFIER, Value) FROM dbo.Split(@AreaIDs,','))

/

USE [Triton2]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 12/11/2012 11:39:39         AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(

    Id int identity(1,1),
    Value uniqueidentifier
) 
AS  
BEGIN 
    While (Charindex(@SplitOn,@List)>0)
    Begin

        Insert Into @RtnValue (value)
        Select 
            Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

What is the datatype of `@AreaIDs` and the parameter defined in `dbo.Split`? If either are `[n]varchar(2000)` there's your answer. [You could look at TVPs for this anyway.](http://www.sommarskog.se/arrays-in-sql-2008.html) — Martin Smith, Dec 11 '12 at 17:23
Can you share the source of your Split function? That's not a built-in function of ms sql server. — Michael Dunlap, Dec 11 '12 at 17:27
I am using an asp.net control parameter to pass the comma dilemmeted string of unique id's. — user1751121, Dec 11 '12 at 17:37
@DigitalD you're on to something here. I was not the original author of this query so i didnt create the split function. I edited my first post with the function. I see the nvarchar(2000) and that explains my error. how do I have it accept a length that would be much larger that I possibly will not know. — user1751121, Dec 11 '12 at 17:40
Change it to accept nvarchar(max) or nvarchar(4000) for the @List parameter. Not sure if there's a huge performance penalty for using max. — Michael Dunlap, Dec 11 '12 at 17:46

score 1 · Answer 1 · edited May 23 '17 at 11:48

As mentioned by @DigitalID it seems like you should be using @List nvarchar(max), and I'd say do so in this case regardless of performance characteristics. It's fairly certain that your application is simply sending more than 2000 characters now, and it would seem to be timebomb coding to assume you won't be sending more than 4k at some point in the not-too-distant future. You know you app best, though.

Generally, the performance issue is because of storage of BLOBs, which SQL Server will usually only do if they go above the 8kB limit. You don't seem to be storing this data, at least in this function. There may also be some performance degradation in terms of plan caching as well, but again, if you need max, then use it.

I also think Martin Smith's suggestion of a different method of passing this parameter is worth a look (TVPs). — Tim Lehner, Dec 11 '12 at 18:37

ASP.NET/SQL Server: Conversion failed when converting from a character string to uniqueidentifier

1 Answers1