Passing table-valued parameter for IN operator

Asked Jul 16 '14 at 16:28

Active Jul 16 '14 at 16:28

Viewed 24 times

How can I pass string parameter with table-value for stored procedure in SQL Server (using c#).

My code:

SqlParameter tvpParam = cmd.Parameters.AddWithValue(
"@inoper", "'New York','Boston'");
        tvpParam.SqlDbType = SqlDbType.NVarChar;

Stored procedure:

...
WHERE
    City IN (@inoper)...

What am I missing here?

asked Jul 16 '14 at 16:28

michael24B

SQL injection prevention mechanism does not allow to put `'` into parametrised string I presume. – Matas Vaitkevicius Jul 16 '14 at 16:31
What should I do? There should be a way to manage that with table-valued parameter. – michael24B Jul 16 '14 at 16:35
Don't use the accepted answer from the duplicate. The best answer there is actually #3. – Sergey Kalinichenko Jul 16 '14 at 16:39
@dasblinkenlight you [meant 7](http://stackoverflow.com/a/6356730/23354), right? ;p (this is meant tongue-in-cheek) – Marc Gravell Jul 16 '14 at 16:47
@michael24B if you specifically want a TVP, you'll want a `DataTable` at the call site; you don't necessarily *need* to use a TVP, though – Marc Gravell Jul 16 '14 at 16:50
@MarcGravell Yes, 7 is also one of several answers that I liked on that page. In fact, the only answer that I truly disliked was the accepted one. – Sergey Kalinichenko Jul 16 '14 at 16:54
@MarcGravell I do not want specifically TVP. I just need the fastest thing to do that. Which is it? – michael24B Jul 16 '14 at 20:18
@Michael I'm going to go ahead and say "dapper", but I'm biased as the author – Marc Gravell Jul 16 '14 at 20:20

0 Answers0