0

I need to filter a sql request by passing a list of id to , this is the command:

var command = "select Software.Name as SoftwareName,SoftwareType.Name as SoftwareType from AssetManagement.Asset as Software inner join AssetManagement.AssetType as SoftwareType on (SoftwareType.Id = Software.TypeId) where Software.Id  in (@P)";


 cmd.Parameters.AddWithValue("@P", authorizedSoftwaresId);

authorizedSoftwaresId is a list of string , containing data like :

"7D23968B-9005-47A9-9C37-0573629EECF9,F1982165-3F6D-4F35-A6AB-05FA116BA279" with that it returns to me just one row, I tried adding quotes foreach value but i got "converstion from string caractere to uniqueidentifier failed " exception

A.HADDAD
  • 1,809
  • 4
  • 26
  • 51
  • 2
    Research how to use table-valued parameters. Another option is to generate your SQL string to have a WHERE clause that is like " ..Software.Id = guidParameter_1 OR Software.Id = guidParameter_2 OR ... etc.". And then add a parameter to the command for each. – Crowcoder Dec 09 '22 at 13:21
  • I don't want to use the database, and the number of paramaters is variable so i can't pass them in different paramaters – A.HADDAD Dec 09 '22 at 13:29
  • 2
    I don't understand what "I don't want to use the database" means. As far as a variable number of parameters, that is not an issue. That's why I used the word "generate". See my similar answer [here](https://stackoverflow.com/questions/74734934/fromsqlinterpolated-and-in-clause/74736196#74736196) – Crowcoder Dec 09 '22 at 13:32
  • Does this answer your question? [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) – Neil Dec 09 '22 at 13:51

1 Answers1

0

This is a pretty common problem and the answer might depend on your database engine and whether you're using ADO.Net, Dapper, etc.

I'll give you some hints from my own experience with this problem on both MS Sql Server and PostgreSQL.

  1. A lot of people think AddWithValue is a devil. You might consider Add instead.
  2. The IN (@P) in your SQL statement might be the source of your problem. Try the Any option instead. See Difference between IN and ANY operators in SQL ; I've had success with this change in similar situations.
  3. If all of your inputs are GUIDs, you might consider changing the type to a collection of GUIDs, although I don't think this is the fix, you have to try everything when you're stuck.
  4. If you have to, you can parse the string version of your collection and add the ticks (') around each value. This choice has consequences, like it may prevent you from using a parameter (@P), and instead construct the final SQL statement you desire (i.e., manually construct the entire WHERE clause through string manipulations and lose the parameter.)

Good luck.

Vic F
  • 1,143
  • 1
  • 11
  • 26