If you are on SQL 2016 or later, there is a very quick solution: SELECT.
In that case you need to split the list into table format, and that is what we will look at in the rest of this chapter.
Unfortunately, not all environments support TVPs, so using a TVP is not always an option. You will find that it is astonishingly simple. NET to SQL Server., The article includes a detailed description of passing a comma-separated list to a TVP. NET, where I give a tutorial of passing TVPs from. If you have never used TVPs before, I have an article, Using Table-Valued Parameters in SQL Server and. After all, you are in a relational database, so why not use a table? That is, you should pass the data in a table-valued parameter (TVP) instead of a delimited list. The best approach in my opinion is to reconsider having a delimited list at all. That is what you will learn in this chapter. Now you know why IN does not work as you hoped for, but if you have a comma-separated list you still need to know to work with it. The value '1,2,3,4' means exactly that string, not a list of numbers. (This explains why you get multiple error messages when you happen to misspell the name of the column left of IN.) There is no magical expansion of a variable values. The parser rewrites the IN expression to a list of OR as soon as it sees it. Val1 etc here can be table columns, variables or constants. Is simply a shortcut for: col = val1 OR col = val2 OR col = val3 OR. IN is an operator and the expression col IN (val1, val2, val3. People who ask why IN does not work have a misconception about IN. SELECT id FROM #test WHERE col IN SELECT returns the row with id = 2, not the others. FROM tbl WHERE col IN answer is that it does work: just look at this: CREATE TABLE #test (id int NOT NULL, I frequently see people in the SQL forums asking why does this not work? DECLARE varchar(23) = '1,2,3,4'
0 kommentar(er)
