Conditional data type conversion for parameters in stored procedure

I read about Cast and Convert functions of SQL Server, but I have a little different requirement. I want to declare all the stored procedure parameters as varchar to eliminate most of the conversion code that is needed in C# while adding parameters with AddWithValue method.

I want to put as much code in the database itself as possible to reduce front-end code. My stored procedure contains INSERT and UPDATE 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?

For e开发者_如何学Pythonxample:

@Udate varchar(10),
@Uamount varchar(50)

INSERT INTO table (Name, Udate, Uamount) 
  VALUES (@Name, Cast(Udate As DateTime), Cast(Uamount As Decimal)

The disadvantage of using CAST in query itself is that the readability of query becomes poor with many CAST functions. Where else I can convert the data types and how to check null values as well?

---

I think, this url might help you.

http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx