title: Type datetime for input parameter in procedure
body: <p>I have created a procedure with this structure but it doesn't work for <code>datetime</code> input parameter </p>
  
  <p>I executed this query but </p>
  
  <pre><code>declare @a datetime
  declare @b datetime 
  
  set @a='2012/04/06 12:23:45'
  set @b='2012/08/06 21:10:12'
  
  exec LogProcedure 'AccountLog', N'test', @a, @b
  </code></pre>
  
  <p>but SQL Server got me this error</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>but when I test with this query it works</p>
  
  <pre><code>  exec LogProcedure 'AccountLog',N'test'
  </code></pre>
  
  <p>Stored procedure code:</p>
  
  <pre><code>alter PROCEDURE LogProcedure
      @TableName VARCHAR(60),
      @SearchString NVARCHAR(50),
      @DateFirst DateTime = '',
      @DateLast DateTime = ''
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @FinalSQL      NVARCHAR(MAX)
  
      SET @FINALSQL = 'SELECT * FROM [' + @TableName + '] where 1=2  '
  
      IF @DateFirst &lt;&gt; '' and @DateLast &lt;&gt; ''
         set @FinalSQL  = @FinalSQL + '  or convert (Date,DateLog) &gt;=     '''+@DateFirst + ' and convert (Date,DateLog) &lt;='''+@DateLast  
  
      SELECT 
         @FinalSQL  = @FinalSQL + ' or  [' + SYSCOLUMNS.NAME + '] LIKE N''%' + @SearchString + '%'' ' 
      FROM SYSCOLUMNS 
      WHERE OBJECT_NAME(id) = @TableName
      AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR','INT','DECIMAL')
      ORDER BY COLID
  
      EXEC(@FinalSQL)
  END 
  </code></pre>
  
  <p>This query is true too</p>
  
  <pre><code>SELECT * 
  FROM AccountLog 
  where 1=2  or convert (Date, DateLog) &gt;= '2012/04/06' 
  and convert (Date, DateLog) &lt;='2012/08/06'
  </code></pre>