1

I want to use a parameter as the Values of a NOT IN operation, against a table with a field called DateID; but I can't get it to work. Take the following code as an example:

DECLARE @NewDateIDs as VARCHAR(1000)

SET @NewDateIDs = '66,67,68,69,70'

SELECT * FROM MyTable WHERE UserID = 1 AND EventID = 19 AND 
CONVERT(VARCHAR(1000), DateID) NOT IN(@NewDateIDs)

When I run the above select statement, all of the records where UserID = 1 and EventID = 19 are returned. What should be returned is one record where UserId = 1, EventID = 19, and DateID = 65.

However, If I run the following select statement, I get the desired results.

SELECT * FROM MyTable WHERE UserID = 1 AND EventID = 19 AND 
CONVERT(VARCHAR(1000), DateID) NOT IN(66,67,68,69,70)

I know I am missing something pretty basic here. Could someone show me how to do this properly and why what I am trying is not working.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
skrantz
  • 39
  • 1
  • 6
  • 1
    [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Lukasz Szozda Feb 20 '19 at 22:50
  • 1
    [comment](https://stackoverflow.com/questions/36642760/why-my-string-containing-query-does-not-work-in-stored-procedure) "People seem to come up with magical beliefs about SQL that, somehow, strings are going to magically turn into executed code, or into multiple separate values. It doesn't work like that in SQL. " – Lukasz Szozda Feb 20 '19 at 22:52
  • What is 66,67,68,69,70 in the second select statement if not a string of numbers? I am fairly new to SQL and am just trying to understand. – skrantz Feb 20 '19 at 23:11
  • @SteveKrantzman it's a set of integers.. –  Feb 20 '19 at 23:17
  • @SteveKrantzman In the second `select` you do, indeed, have a list of integer values. (It is a mystery as to why you would want to convert `DateID` from an integer to a string before trying to match it against a list of integers.) The first `select` will match any `DateID` that happens to be the _string_ `'66,67,68,69,70'`, which is not likely. It could just as well be `( 'red', 'yellow', 'blue' )` to match any _single_ color or `( 'red,yellow,blue' )` to match _only_ the string `'red,yellow,blue'`. – HABO Feb 20 '19 at 23:22
  • @HABO My first attempt was to indeed compare DateID as an Int to a list of integers. `SELECT * FROM MyTable WHERE UserID = 1 AND EventID = 19 AND DateID NOT IN(@NewDateIDs)` but this threw the following error: _Conversion failed when converting the varchar value '66,67,68,69,70' to data type int._. This lead me to believe that the Param contained a string not a list of integers, and therefore I had to convert DateID to a string. – skrantz Feb 21 '19 at 16:17

1 Answers1

0

You can use a dynamic sql query to achieve that:

DECLARE @strQuery as VARCHAR(4000)

SET @strQuery = 'SELECT * FROM MyTable WHERE UserID = 1 AND EventID = 19 AND 
DateID NOT IN(' + @NewDateIDs + ')'

EXEC(@strQuery)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hadi, I awarded you the correct answer as it does indeed provide a solution to my question. I did find a great article that discusses this very topic : http://www.sommarskog.se/arrays-in-sql-2005.html. This is what you may have suggested had I not converted `DateID` to a string in the original post : `SELECT * FROM MyTable WHERE UserID = 1 AND EventID = 19 AND DateID NOT IN(SELECT convert(int, value) FROM string_split(@NewDateIDs, ','))`. Now that I have a better understanding, I will look for a more performant method other than NOT IN to solve my overall goal for the stored procedure. – skrantz Feb 21 '19 at 17:17