Reputation: 604
I'm using a stored procedure - how can I pass a string like:
'White','Black','Green'
I'm trying to execute a procedure as follows:
var data = new List<dynamic>();
string colorstr = "'White','Black','Green'";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = $"exec GetData '{colorstr }'";
data = connection.Query(sql).AsList();
connection.Close();
}
This gives an error:
incorrect syntax near 'White'
So the question is how can I pass a comma separated string with single quotes to a stored procedure?
Upvotes: 0
Views: 2363
Reputation: 1842
You're actually looking for how pass an array as a parameter to a stored procedure. SQL Server doesnt explicity support arrays as a parameter but there are ways to get it to work using XML or using the List variable type.
For more information see: How to pass an array into a SQL Server stored procedure
Upvotes: 0
Reputation: 19838
You are creating a SQL Injection problem. Instead, use SQL Command like below. You avoid the problem and SQL injection
using (SqlCommand cmd = new SqlCommand("connectionString", con)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@colorstr", SqlDbType.VarChar).Value = "'White','Black','Green'";
con.Open();
cmd.ExecuteNonQuery();
}
I don't know your parameter name so I used colorstr
, and parameter type, so I used SqlDbType.VarChar
. Check it in DB and fix.
Upvotes: 3