StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIncosistency between MS Sql 2k and 2k5 with columns as function arguments
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload