0

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!

jamesmhaley
  • 44,484
  • 11
  • 36
  • 49

2 Answers2

3

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.

Jimmy Stenke
  • 11,140
  • 2
  • 25
  • 20
  • Which is what I said in my reply with "split out the strings". –  Oct 21 '09 at 08:46
  • To pass in the current tags as a split string I send in this (from classic asp): EXEC GetTagSuggest 'Web','''Web'',''Create'''. That, when selected, returns: 'Web','Create'. But when passed in like this: t.vTagName NOT IN(@vCurrentTags), it still doesn't work. How in SQL could I split the string? – jamesmhaley Oct 21 '09 at 09:11
  • @randolph: I understand that, but I required a little more information that one line :) – jamesmhaley Oct 21 '09 at 09:13
  • If you cannot split the string, perhaps look at the dynamic SQL method I described. –  Oct 21 '09 at 09:17
2

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