<p>I'm having trouble getting the following to work in SQL Server 2k, but it works in 2k5:</p>

<pre><code>--works in 2k5, not in 2k

create view foo as    
SELECT  usertable.legacyCSVVarcharCol as testvar     
FROM  usertable   
WHERE rsrcID in
    (  select val     
       from
       dbo.fnSplitStringToInt(usertable.legacyCSVVarcharCol, default)
    )

--error message:    
Msg 170, Level 15, State 1, Procedure foo, Line 4    
Line 25: Incorrect syntax near '.'.
</code></pre>

<p>So, legacyCSVVarcharCol is a column containing comma-separated lists of INTs. I realize that this is a huge WTF, but this is legacy code, and there's nothing that can be done about the schema right now. Passing "testvar" as the argument to the function doesn't work in 2k either. In fact, it results in a slightly different (and even weirder error):</p>

<pre><code>Msg 155, Level 15, State 1, Line 8

'testvar' is not a recognized OPTIMIZER LOCK HINTS option.
</code></pre>

<p>Passing a hard-coded string as the argument to fnSplitStringToInt works in both 2k and 2k5.</p>

<p>Does anyone know why this doesn't work in 2k? Is this perhaps a known bug in the query planner? Any suggestions for how to make it work? Again, I realize that the real answer is "don't store CSV lists in your DB!", but alas, that's beyond my control.</p>

<p>Some sample data, if it helps:</p>

<pre><code>INSERT INTO usertable (legacyCSVVarcharCol) values ('1,2,3');
INSERT INTO usertable (legacyCSVVarcharCol) values ('11,13,42');
</code></pre>

<p>Note that the data in the table does not seem to matter since this is a syntax error, and it occurs even if usertable is completely empty.</p>

<p>EDIT: Realizing that perhaps the initial example was unclear, here are two examples, one of which works and one of which does not, which should highlight the problem that's occurring:</p>

<pre><code>--fails in sql2000, works in 2005

SELECT t1.* 
FROM usertable t1
WHERE 1 in 
    (Select val 
     from 
     fnSplitStringToInt(t1.legacyCSVVarcharCol, ',')
    )

--works everywhere:

SELECT t1.*   
FROM usertable t1
WHERE 1 in 
    ( Select val 
      from 
      fnSplitStringToInt('1,4,543,56578', ',')
    )
</code></pre>

<p>Note that the only difference is the first argument to fnSplitStringToInt is a column in the case that fails in 2k and a literal string in the case that succeeds in both.</p>

[{'answer_id': 26577, 'author': 'wcm', 'author_id': 2173, 'author_profile': 'https://Stackoverflow.com/users/2173', 'pm_score': 0, 'selected': False, 'text': "<p>I don't think functions can have default values in functions in SS2K.</p>\n\n<p>What happens when you run this SQL in SS2K?</p>\n\n<pre><code>select val     \nfrom dbo.fnSplitStringToInt('1,2,3', default)\n</code></pre>\n"}, {'answer_id': 49638, 'author': 'MobyDX', 'author_id': 3923, 'author_profile': 'https://Stackoverflow.com/users/3923', 'pm_score': 2, 'selected': True, 'text': "<p>Passing column-values to a table-valued user-defined function is not supported in SQL Server 2000, you can only use constants, so the following (simpler version) would also fail:</p>\n\n<pre><code>SELECT *, (SELECT TOP 1 val FROM dbo.fnSplitStringToInt(usertable.legacyCSVVarcharCol, ','))\nFROM usertable\n</code></pre>\n\n<p>It will work on SQL Server 2005, though, as you have found out.</p>\n"}]
['https://Stackoverflow.com/questions/26478', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2327/']