CODEWITHSUNDEEP
c#asp.netsql-serversqlcommand
HydraCc
HydraCc

Reputation: 41

Sending multiple info to SQL

I’m sorry about the nonsense question title, here is what i intended to asked:
in c#:
So i have a list that has integers(id’s),
and i have a query string:

myList //the list that has id’s in it.
string QueryString = “SELECT * FROM dbo.UserInfo WHERE id!= @idList”;

What im tryin to do is assign every item in the myList to the SqlCommand parameter:

SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand(QueryString,con)
cmd.Parameters.AddWithValue(“@idList”,myList)

So in a nutshell, i want to choose all from dbo.UserInfo where id not equals to any item from myList.
Is there a way to do this or do i have to run the query for every item?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Basically, you need left join or not exists. If @idlist is a string with comma separated values, then one method is:

select i.value as missing_id
from string_split(@idlist, ',') i left join
     dbo.UserInfo ui
     on ui.id = i.value
where ui.id is null;

Note that string_split() has been available since SQL Server 2016. There are other (somewhat more cumbersome methods) in earlier versions of SQL Server.

Upvotes: 0

Related Questions