using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void InsertWithParameters()
  {
    SqlConnection conn = new SqlConnection("context connection=true");
    conn.Open();
    SqlCommand cmd = conn.CreateCommand();

    // set the command text
    // use names as parameter markers
    cmd.CommandText =
     "insert into jobs values(@job_desc, @min_lvl, @max_lvl)";

    // names must agree with markers
    // length of the VarChar parameter is deduced from the input value
    cmd.Parameters.Add("@job_desc", SqlDbType.VarChar);
    cmd.Parameters.Add("@min_lvl", SqlDbType.TinyInt);
    cmd.Parameters.Add("@max_lvl", SqlDbType.TinyInt);

    // set values
    cmd.Parameters[0].Value = "A new job description";
    cmd.Parameters[1].Value = 10;
    cmd.Parameters[2].Value = 20;

    // execute the command
    // should return 1 row affected
    int rows_affected = cmd.ExecuteNonQuery();

  }
};
