Incosistency between MS Sql 2k and 2k5 with columns as function arguments

Question

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

--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 '.'.

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):

Msg 155, Level 15, State 1, Line 8

'testvar' is not a recognized OPTIMIZER LOCK HINTS option.

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

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.

Some sample data, if it helps:

INSERT INTO usertable (legacyCSVVarcharCol) values ('1,2,3');
INSERT INTO usertable (legacyCSVVarcharCol) values ('11,13,42');

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.

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:

--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', ',')
    )

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.

Answer 1

I don't think functions can have default values in functions in SS2K.

What happens when you run this SQL in SS2K?

select val     
from dbo.fnSplitStringToInt('1,2,3', default)

Answer 2

@wcm: Removing the default value still results in the same error, so I don't think that's the issue here.

Answer 3

Hi pointatstar,

I suppose what I was getting at is that the function is expecting an actual value instead of the default keyword. What is the function expecting? Is it the delimiter being used or something?

Sorry for the confusion.

Answer 4

@wcm: providing an actual value in place of the default keyword results in the same problem. And yes, the second argument being passed is the delimiter being used. Sorry for being unclear about that.

Also, in case it was unclear - passing a literal string as the first argument to the function works, the problem occurs only when a column name (or alias) is passed (as shown in the initial question posting).

Answer 5

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:

SELECT *, (SELECT TOP 1 val FROM dbo.fnSplitStringToInt(usertable.legacyCSVVarcharCol, ','))
FROM usertable

It will work on SQL Server 2005, though, as you have found out.