Passing a stored procedure as a string

Question

How do I pass a stored procedure along with parameters as a string to a function?

I tried this code but no luck..

This is the Business Access Layer code

 try
 {
     string Query_string = "SP_InsertOffer_Tab @offer_name ='" + this.offer_name +"',  @offer_price = " + this.offer_price + ",@start_date = '" + this.start_date + 
 "',@end_date = '" + this.end_date + "'";

     int result = DbAcess.Insert_Query(Query_string);
     return result;
 }
 catch (Exception ex)
 {
    throw ex;
 }
 finally
 {
    DbAcess = null;
 }

Database layer code is as follows

public int Insert_Query(string strSQL)
{
    SqlConnection con = new SqlConnection();
    con = OpenConnection();

    try
    {
        sqlcmd = new SqlCommand();
        sqlcmd.Connection = con;
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.CommandText = strSQL;

        int Result = sqlcmd.ExecuteNonQuery();
        return Result;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
    }
}

And please read up on [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) - string concatenation for SQL is bad. — Oded, Mar 13 '13 at 19:54
`CommandType.StoredProcedure` it's Text you're passing so that needs to be changed `CommandType.Text` is what I am seeing — MethodMan, Mar 13 '13 at 19:54
@RayCheng - Just don't do anything in this case. Let the exception bubble up. — Oded, Mar 13 '13 at 19:55
Could not find stored procedure 'SP_InsertOffer_Tab @offer_name ='Winter-Special',@offer_price = 400,@start_date = '3/6/2013',@end_date = '3/13/2013'' — user2115640, Mar 13 '13 at 19:56
i tried making it commandType.Text but again da same exception throws — user2115640, Mar 13 '13 at 19:57
Are you sure the database you are connecting to has this stored procedure? And that the account the application is running under has permissions to execute it? — Oded, Mar 13 '13 at 19:57
You're not calling the procedure correctly. A quick search would have shown you that. Try this SO thread's answer http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp — , Mar 13 '13 at 19:58
@Oded, if you just `throw`, the exception will bubble up. however, `throw ex` will mess up the stack. — Ray Cheng, Mar 13 '13 at 20:00
do you have a stored procedure already on the Database side..? if so then it doesn't make sense why you are trying to Execute it by calling the Stored Procedure..you need to define the `sqlcmd.CommandText = "SP_InsertOffer_Tab"` also you need to use `Paramerterized Queries` for example `sqlcmd.Parameters.AddWithValue(@ParamName, ParamValue); that query should not execute correctly in it's current state.. — MethodMan, Mar 13 '13 at 20:01
@RayCheng - With the code as it stands, there is no need for a `catch` block **at all** - that was the point I was trying to make. — Oded, Mar 13 '13 at 20:02
Alter this method `public int Insert_Query(string strSQL)` and pass in the following `( offer_namestring,offer_price, end_date)` — MethodMan, Mar 13 '13 at 20:05

score 3 · Answer 1 · answered Mar 13 '13 at 19:57

Instead of passing strSQL as the CommandText, where strSQL is the string you create in the first code block (I think...), just pass the SP name as the CommandText and then add Parameters to your sqlcmd object.

SqlParameter p = new SqlParameter("@ParameterName", parametervalue));
sqlcmd.Parameters.Add(p);

Steve · Answer 2 · 2013-03-13T20:29:20.473

Just to try to RESOLVE your problem, but BEWARE that this method is very dangerous and NOT RECOMMENDED for the Sql Injection problem.

string Query_string = "EXEC SP_InsertOffer_Tab @offer_name ='" + 
            this.offer_name +"',  @offer_price = " + 
            this.offer_price + ",@start_date = '" + 
            this.start_date + "',@end_date = '" + this.end_date + "'";

and change the CommandType to Text.

A better approach would be to change the Insert_Query method

public int Insert_Query(string strSQL, SqlParameter[] prm)
{
    using(SqlConnection con = OpenConnection())
    {
        sqlcmd = new SqlCommand(strSql, con);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddRange(prm)
        int Result = sqlcmd.ExecuteNonQuery();
        return Result;
    }
}

then call it in this way

SqlParameter[] prms = new SqlParameter[]
{
   new SqlParameter("@offer_name", SqlDbType.NVarChar),
   new SqlParameter("@offer_price", SqlDbType.Money),
   new SqlParameter("@start_date", SqlDbType.SmallDateTime),
   new SqlParameter("@end_date", SqlDbType.SmallDateTime)
};
prms[0].Value = this.offer_name;
prms[1].Value = this.offer_price;
prms[2].Value = this.start_date;
prms[3].Value = this.end_date;
int result = DbAcess.Insert_Query(Query_string, prms);

Passing a stored procedure as a string

2 Answers2