title: SQL server - Conversion failed error but not really
body: <p>I've got a user-defined function to split lists of integers into a table of values.  I'm using this to parse input to select a set of records for a given set of types or statuses.</p>
  
  <p>This works:</p>
  
  <pre><code>select * from RequestStatus
  where RequestStatusUID in (select [value] from dbo.SplitIDs('1,2,3', ','))
  </code></pre>
  
  <p>This does not:</p>
  
  <pre><code>select * from Requests
  where RequestStatusUID in (select [value] from dbo.SplitIDs('1,2,3', ','))
  </code></pre>
  
  <p>The Requests query returns the error "<strong>Conversion failed when converting the varchar value '1,2,3' to data type int.</strong>" RequestStatusUID on both tables are int columns. Both Explain plans look the same to me.  The function is working perfectly the same way in unrelated queries.  So far as I can tell it's only the Requests table that has the problem.</p>
  
  <pre><code>CREATE TABLE [dbo].[Requests]  ( 
  [RequestUID]        int IDENTITY(1,1) NOT NULL,
  [UserUID]           int NOT NULL,
  [LocationUID]       int NOT NULL,
  [DateOpened]        date NULL,
  [DateClosed]        date NULL,
  [RequestStatusUID]  int NOT NULL,
  [DiscussionUID]     int NULL,
  [RequestTypeUID]    int NOT NULL,
  [RequestNo]         varchar(16) NOT NULL,
  [LastUpdateUID]     int NOT NULL,
  [LastUpdated]       date NOT NULL,
  CONSTRAINT [PK_Requests] PRIMARY KEY NONCLUSTERED([RequestUID])
  </code></pre>
  
  <p>It does work if I use a different function that returns varchars and I convert the RequestStatusUID column to a varchar as well:</p>
  
  <pre><code>select * from Requests
  where cast(RequestStatusUID as varchar(4)) in (select [value] from dbo.Split('1,2,3', ','))
  </code></pre>
  
  <p>For reference, the SplitIDs function I'm using (a modified version of <a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648" rel="nofollow">Arnold Fribble's solution</a>).  The Split function is the same without the cast as int at the end:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[SplitIDs] ( @str VARCHAR(MAX), @delim char(1)=',' )
  RETURNS TABLE
  AS
  RETURN
  (
      with cte as (
          select 0 a, 1 b
          union all
          select b, cast(charindex(@delim, @str, b) + 1 as int)
          from cte
          where b &gt; a
      )
      select cast(substring(@str,a,
      case when b &gt; 1 then b-a-1 else len(@str) - a + 1 end) as int) [value]      
      from cte where a &gt;0
  )
  </code></pre>
  
  <p>I can use the convert-to-strings solution but I'd really like to know why this is failing in the first place.</p>