title: Filtering Data Between Two Dates in Dapper ORM
body: <p>i am using Dapper ORM for filtering and fetching the collection of objects. when i am trying to filter objects by date i am getting SQL error. but the same query is running  perfect in SQL server 2012. below is my code</p>
  
  <pre><code>                DataTableSettings DS = new DataTableSettings();
                  DataTableElements DataTableElements = new DataTableElements();
                  DataTableElements.Limit = param.iDisplayLength;
                  DataTableElements.Start = param.iDisplayStart;
                  DataTableElements.SortColumn = "a.Ser_Id"; //default sorting column
                  DataTableElements.AType = "DESC";
                  DataTableElements.ListingFields = "Ser_SlNo,a.Ser_Id as SERID,a.Ser_LogNo AS SLNO,Cas_Name,a.Ser_CallReason AS RSON," + 
                                                    "Ser_AssignedTo,Ser_CallTime,Ser_CallDate,a.Cus_Id AS CUSID,a.Cas_Id AS CSID," +
                                                    "Cus_Name,Add_Landmark,a.Prm_Id AS PRMID,Prm_Model, c.Cut_Id AS CUTID,Cut_Name";
                  DataTableElements.TableName = "ServiceCalls as a inner join CallStatus as b on a.Cas_Id = b.Cas_Id" + 
                                                    "inner join Customers as c on a.Cus_Id = c.Cus_Id" +  
                                                    "inner join Products as d on a.Prd_Id = d.Prd_Id" + 
                                                    "inner join AddressDetailes as e on a.Add_id = e.Add_id" +
                                                    "inner join Companies as f on a.Com_Id = f.Com_Id" + 
                                                    "inner join ProductModels as g on a.Prm_Id = g.Prm_Id" + 
                                                    "inner join ProductSerials as h on a.Prs_Id = h.Prs_Id" + 
                                                    "inner join CustomerTypes as i on c.Cut_Id = i.Cut_Id" + 
                                                    "inner join UserServices as j on a.Ser_Id = j.Ser_Id" +
                                                    "where CONVERT(date, Ser_CallDate,101) BETWEEN CONVERT(date,'3/1/2016 12:00:00 AM', 101) AND CONVERT(date,'3/31/2016 12:00:00 AM', 101)" +
                                                    "and j.Usr_Id = 21";
                    var str = DS.GetDataTableElements(DataTableElements);
  
  
   public dynamic GetDataTableElements(DataTableElements DataTableElements)
          {
              _db = new SqlConnection(db.Database.Connection.ConnectionString);
              string Sql = "exec SelectFomTable    @TableName ='" + DataTableElements.TableName + "',@Limit =" + DataTableElements.Limit + ",@Start =" + DataTableElements.Start + ",@SortColumn ='" + DataTableElements.SortColumn + "'   ";
  
              Sql += ",@ListingFields='" + DataTableElements.ListingFields + "'";
              Sql += ",@AType='" + DataTableElements.AType + "'";
              Sql += ",@Filter='" + DataTableElements.Filter + "'";
              dynamic retuns = _db.Query&lt;dynamic&gt;(Sql).ToList();
              return retuns;
          }
  </code></pre>
  
  <p>and my stored procedure is as follows</p>
  
  <pre><code>ALTER PROCEDURE  [dbo].[SelectFomTable] 
  -- Add the parameters for the stored procedure here
  (@TableName varchar(max),@Limit Int=10,@Start int=0,@SortColumn varchar(50),@Filter varchar(max)=null,@ListingFields varchar(max)=null,@AType varchar(max)=null )
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  Declare @sts varchar(max);
  Declare @List varchar(max);
  Declare @Assend varchar(max);
  Declare @End int ;
  set @End =@Limit+@Start;
  set @List =isnull(@ListingFields,'*');
  set @Assend =isnull(@AType,'ASC');
  set @sts= 'WITH tab as (select  ROW_NUMBER() OVER(ORDER BY '+@SortColumn+'  '+@Assend+' ) AS RowIncrement, '+@List+'  from '+@TableName+@Filter+'  ),
    tab1 as (select count(*) as TotalCount  from '+@TableName+@Filter+' )
                 select * from tab,tab1 where RowIncrement between '+CONVERT(varchar(10), @Start) +' and '+CONVERT(varchar(10), @End);
    set @sts=@sts+@Filter;
  --if ( @Filter is not null)
  --begin
  --set @sts=@sts+@Filter;
  --end 
  print @sts;
      exec(@sts);
  </code></pre>
  
  <p>and the error i am getting is as follows</p>
  
  <pre><code>{"Incorrect syntax near '3'."}
  </code></pre>
  
  <p>please help.....</p>