How to perform a WHERE similar to "NOT IN" with a list of value coming from a HTTP FORM?

Question

I have web application developed in C#, where a page is showing the output of a SQL query; the query is a linq call to a store procedure and I am using SQL Server 2008.

One of the column display the tags associated with the result line; on the same page a list of check-boxes is displayed, each check-box correspond to a tag, and if the user turn on or off one or multiple check-box I want to filter the query.

My simplest SQL solution would be to us "NOT IN" like the following:

select * from [mytable] where [tags] not in ('tag1','tag2, etc...)

Given I convert the FORM POST to a string with comma separated values. Example:

string tags = ParseFormAndConvertCheckBoxToCSV(Page.Request.Form);
string sqlcmd = "select * from [mytable] where [tags] not in (" + tags + ")";

But I don't want to dynamically build the SQL because as far as I know that would be bad.

I can imagine few way of splitting the string with comma separated list of values in SQL nd store the values into a in-memory table:

declare @tagscsv nvarchar(MAX)
declare @notags TABLE(Value nvarchar(50))

set @tagscsv = 'taxi,ivf'

Declare @x XML 
select @x = cast('<A>'+ replace(@tagscsv,',','</A><A>')+ '</A>' as xml)

insert into @notags
select t.value('.', 'nvarchar(50)') as v from @x.nodes('/A') as x(t)

select * from [mytable] where [tags] not in (select * from @notags)

...but this too sounds like a dirty trick.

To me it looks like this should be a very common situation, and I figure out a lot of people out there faced this problem in the past, but searching on google I could not find an elegant solution.

Anyone can help?

tags ='A' OR tags='B' etc., is that what you are looking for? — kosa, Apr 27 '12 at 18:10
What do you mean by `I don't want to dynamically build the SQL because that would be bad.` ?? — JNK, Apr 27 '12 at 18:12
Try my solution [at this question][1]. You are right, this is a very common question. [1]: http://stackoverflow.com/questions/10355507/sql-query-not-returning-results/10355594#10355594 — therealmitchconnors, Apr 27 '12 at 18:14
Please provide additional context. Are you using string SQL queries, linq to sql, something else? What platform is the webpage built on (ASP, APSX, MVC, PHP, something else)? Also, what makes a split comma separated list a dirty trick? — Trisped, Apr 27 '12 at 18:25
I edited the question to provide more context, yes I am using C#, linq to entities, and a store procedure for SQL server 2008; but I have the feeling my question is of a general nature and would apply to other programming frameworks and databases as well. — Max Favilli, Apr 27 '12 at 18:44
I have updated my answer. If you have to use a Stored Procedure then your answer is good (though I would use a loop rather then build an XML tree). — Trisped, Apr 27 '12 at 19:26

Trisped · Accepted Answer · 2012-04-27T19:17:46.423

Edit: Missed the stored procedure part. With stored procedures you do not have a lot of options. I usually do what you did, split the comma seperated string and save the values to a temp table (or table variable).

CREATE PROCEDURE SplitAndStuff
    @List nvarchar(MAX) = NULL,
    @SplitOn nvarchar(5) = ','  --This can be replaced with a literal if it is always a comma.
AS
BEGIN
    DECLARE @Pos int;
    DECLARE @SplitOnLength int;
    DECLARE @Results TABLE (value nvarchar(MAX))
    SET @SplitOnLength = DATALENGTH(@SplitOn) / 2;

    IF (RIGHT(@List, @SplitOnLength) <> @SplitOn) SET @List = @List + @SplitOn; --Add trailling split string if there is not one already.

    SET @Pos = CHARINDEX(@SplitOn, @List, 1)                                    --Initalize for loop.  (The starting position returned is 1-based, not 0-based.)

    WHILE @Pos > 0
    BEGIN
        INSERT INTO @Results (value) SELECT CAST(SUBSTRING(@List,1,@Pos-1)AS int);

        SET @List = SUBSTRING(@List, @Pos+@SplitOnLength, DATALENGTH(@List) / 2);
        SET @Pos = CHARINDEX(@SplitOn, @List, 1);
    END
END
GO

If your stored procedure is not doing anything else (besides the look up) you could use my original LINQ answer below:

Since you are using LINQ you need to split tagscsv and use the resulting array to perform an "where in" query:

string[] tags = tagscsv.Split(',');
var output = from q in db.Table where !tags.Contains(q) select q;

How to perform a WHERE similar to "NOT IN" with a list of value coming from a HTTP FORM?

1 Answers1