C# how to call “sp_executesql” with custom parameters and sql body

Question

I am trying to execute a dynamic SQL from C# and I want to use sp_executesql because I want to pass a list of strings to be used for an IN statement in the dynamic SQL passed to sp_executesql.

The thing is that I don't know exactly how to do this.

So far I did it like this:

using (var dbCommand = databaseConnection.CreateCommand())
{
    dbCommand.CommandType = CommandType.StoredProcedure;
    dbCommand.CommandText = "sp_executesql";

    var sqlParam = dbCommand.CreateParameter();
    sqlParam.DbType = DbType.String;
    sqlParam.ParameterName = "stmt";
    sqlParam.Value = sql.SQL;
    dbCommand.Parameters.Add(sqlParam);

    var incidentIdParam = dbCommand.CreateParameter();
    incidentIdParam.ParameterName = "incidentId";
    incidentIdParam.DbType = DbType.Int32;
    incidentIdParam.Value = arguments.filterAttributes.incidentId;
    dbCommand.Parameters.Add(incidentIdParam);

    dbCommand.ExecuteReader();
}

Is it possible like this?

There's little point to ever executing `sp_executesql` from client code, because client code can already parameterize any statement and perform textual replacement where necessary. You will have (almost) the same effect by setting `CommandText` to `sql.SQL` and `CommandType` to `Text`. Note that you *cannot* parameterize an `IN` clause even with `sp_executesql` -- you can only replace it textually, or use a table-valued parameter. See [here](https://stackoverflow.com/q/337704/4137916), but be sure to read past the accepted answer (which is a dirty hack). — Jeroen Mostert, Nov 07 '18 at 15:04
I figured out how to do it, you need to generate a parameter for each element in the IN clause: https://stackoverflow.com/questions/9384446/how-to-pass-sqlparameter-to-in — Alecu, Nov 07 '18 at 15:41

Miamy · Accepted Answer · 2018-11-07T15:12:20.993

1

As an option, you can format your SQL on the client side and pass to the stored procedure ready string. For example:

sql.SQL = "UPDATE ORDERS SET STATUS = 1 WHERE ID = %param%";

then

sqlParam.Value = sql.SQL.Replace("%param%", arguments.filterAttributes.incidentId.ToString());

edited Nov 07 '18 at 15:12

answered Nov 07 '18 at 15:04

Miamy

2,162
3
15
32

2

Why not simply use parameters? Your method is vulnerable to a SQL injection attack. Avoid shortcuts w hen you can. – Mark Kram Nov 07 '18 at 15:10
Use of statistics histogram vs density vector. – user2368632 Jun 22 '23 at 20:03

C# how to call “sp_executesql” with custom parameters and sql body

1 Answers1