title: Execute a SQL Server stored procedure with dynamic parameters using Entity Framework
body: <p>We want to execute a stored procedure using EF.</p>
  
  <p>Our approach is to provide input parameters dynamically as shown below :</p>
  
  <pre><code>PropertyInfo[] props = search.GetProperties();
  
  foreach (var item in props)
  {
       object itemValue = item.GetValue(SearchCondition, null);
  
       if (itemValue == null)
          parameterList.Add(new SqlParameter(string.Format("@{0}", item.Name), DBNull.Value));
       else
          parameterList.Add(new SqlParameter(string.Format("@{0}", item.Name), itemValue.ToString()));
  }
  </code></pre>
  
  <p>and here are other common inputs:</p>
  
  <pre><code>parameterList.Add(new SqlParameter("@order", (object)"desc"));
  parameterList.Add(new SqlParameter("@Page", page));
  parameterList.Add(new SqlParameter("@PageSize", rows));
  </code></pre>
  
  <p>and also output parameter:</p>
  
  <pre><code>parameterList.Add(new SqlParameter()
  {
      DbType = DbType.Int32,
      ParameterName = "@TotalCount out",
      SqlValue = DBNull.Value,
      Direction = ParameterDirection.Output
  });
  </code></pre>
  
  <p>and here is execution code (running the stored procedure)</p>
  
  <pre><code>object[] para= parameterList.ToArray&lt;object&gt;();
  
  var records = context.Database.SqlQuery&lt;CustomerInformationReturnedList&gt;(string.Format(@"Exec RTI.RptCustomerInformation {0}", parameters), para).ToList();
  </code></pre>
  
  <p>When we run it there is an exception that says:</p>
  
  <blockquote>
    <p>incorrect syntax near int</p>
  </blockquote>
  
  <p>This is our executed stored procedure in SQL which runs correctly in SQL Server Management Studio:</p>
  
  <pre><code>EXEC    @return_value = [RTI].[RptCustomerInformation]
      @AccountNumber = NULL,
      @CardNumber = NULL,
      @CustomerNumber = NULL,
      @CustomerType = NULL,
      @NationalNumber = NULL,
      @Name = NULL,
      @FamilyName = NULL,
      @IdentityNumber = NULL,
      @IdSerial = NULL,
      @NationalityType = NULL,
      @InhabitancyType = NULL,
      @JobType = NULL,
      @c13 = NULL,
      @CustomerDefined = NULL,
      @OpenerBranch = NULL,
      @CodePosti = NULL,
      @RegisterNumber = NULL,
      @order = NULL,
      @Page = NULL,
      @PageSize = NULL,
      @TotalCount = @TotalCount OUTPUT
  
  SELECT  @TotalCount as N'@TotalCount'
  </code></pre>