title: Conditional data type conversion for parameters in stored procedure
body: <p>I read about <a href="http://msdn.microsoft.com/en-us/library/ms191530.aspx" rel="nofollow">Cast and Convert</a> functions of SQL Server, but I have a little different requirement. I want to declare all the stored procedure parameters as <code>varchar</code> to eliminate most of the conversion code that is needed in C# while adding parameters with <code>AddWithValue</code> method.</p>
  
  <p>I want to put as much code in the database itself as possible to reduce front-end code. My stored procedure contains <code>INSERT</code> and <code>UPDATE</code> queries. I want to convert the data type of parameters in queries itself or in any other better way out. CAST function works well for conversion but what if I also want to pass NULL if the parameter has no value?</p>
  
  <p>For example:</p>
  
  <pre><code>@Udate varchar(10),
  @Uamount varchar(50)
  
  INSERT INTO table (Name, Udate, Uamount) 
    VALUES (@Name, Cast(Udate As DateTime), Cast(Uamount As Decimal)
  </code></pre>
  
  <p>The disadvantage of using <code>CAST</code> in query itself is that the readability of query becomes poor with many <code>CAST</code> functions. Where else I can convert the data types and how to check null values as well?</p>