SQL Server 2008 NOT IN() seems to fail

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" 开发者_Go百科NOT IN "Web, etc..."..

Thanks in advance!

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.

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