SQL Server 2008 NOT IN() seems to fail

Question

Hey guys,

I am building a suggestive tagging system (sounds seedy), I pass in a string to the db and if there is a match it sends back that tag. I want to stop duplicate tags, so if one tag is WEB, I only want it to be returned once. So if I had WEB as a tag and then add WEEKLY, typing "WE" doesn't bring up WEB for a second time as a suggestion.

But, seem to be not working. I send the current tags and use NOT IN() to remove any duplicates. Below is the SP code:

SELECT  TOP 1 t.vTagName As vTagName
FROM  Tags t
WHERE  t.vTagName LIKE @vTagName+'%'
AND   t.nTagPortalId = @nPortalId
AND   t.vTagName NOT IN(@vCurrentTags)
ORDER BY vTagName ASC

And this is what get's passed in:

EXEC GetTagSuggest 'We','Web,Print,Design,Advertising,Revenue'

The response to this query is vTagName = Web. Obviously, this is not a correct result as it should be vTagName LIKE "We" NOT IN "Web, etc..."..

Thanks in advance!

Answer 1

You need to split out the strings, or convert the entire SQL statement to dynamic SQL.

EDIT:

Split out variables per the other example, or do dynamic SQL.

This is not best practice, but is simply to give you an idea:

DECLARE @sql nvarchar(max) = 'SELECT  TOP 1 t.vTagName As vTagName '
SELECT @sql = @sql + 'FROM  Tags t '
SELECT @sql = @sql + 'WHERE t.vTagName LIKE ' + @vTagName + '% '
SELECT @sql = @sql + 'AND   t.nTagPortalId = ' + @nPortalId + ' '
SELECT @sql = @sql + 'AND   t.vTagName NOT IN(' + @vCurrentTags + ') '
SELECT @sql = @sql + 'ORDER BY  vTagName ASC'

EXEC sp_executesql @sql, @vTagName, @nPortalId, @vCurrentTags

Answer 2

The IN statement doesn't work like that.

What you will have to do is

t.vTagName not in ('Web','Print','Design','Advertising','Revenue')

Having it in one variable won't work in that case.

Answer 3

Erland Sommarskog has a good article on various ways to perform these types of search