Passing .NET data types in Parameters to Stored Procedure

Structured Query Language is very forgiving in its automatic type conversion, sometimes too much so. Recently I encountered a stored procedure, written by a co-worker, that passed a GUID cast as a string to a varchar(255) stored procedure parameter, which was then directly inserted into a table that expected a uniqueidentifier (SQL for GUID). While the team quickly agreed this sort of type coercion was something best avoided, they also asked me for a mapping guide for .NET data types to SQL Server data types. This blog post is the answer to that request.
Generally speaking, most .NET data types have a counterpart in the SQL world and vice versa, although there are some differences and caveats. For example, there is no Unsigned integer in SQL, and SQL DateTimes have different range limitations from their .NET counterparts. More on that in a little while.
Here is the list of .NET data types and their SQL counterparts. This list is adapted from SQL Server documentation and is presented in a .NET-centric way. It is not necessarily inclusive of all defined SQL data types; rather the intention is to provide a data type for each .NET data type.

• Boolean: pass to a bit. Note that SQL Server stores up to 8 bits within a given table row into each byte of data on the disk. So storing 8 boolean flags has the same storage cost as a single byte field.
• Byte[]: pass to a binary(n) if the size is always the same or a varbinary(n) if the size will vary and not exceed 8000 bytes. For Byte[] buffers > 8000 bytes, pass to a varbinary(max).
• byte: pass to a TinyInt.
• Byte[1]: pass to a binary(1) or varbinary(1).
• char, char[1]: pass to a nchar(1) or nvarchar(1).
• char[], string: pass to a nchar(n) if the size is always the same or a nvarchar(n) if the size will vary and not exceed 8000 bytes. For char[] strings > 8000 bytes long, pass to a nvarchar(max).
• datetime: Pass to a DateTime or a SmallDateTime, depending on the precision needed or the time range to be stored. The DateTime stores Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds. The SmallDateTime stores Date and time data from January 1, 1900, to December 31, 2079, with an accuracy of one minute. Note that attempting to store the .NET value DateTime.MinValue in an SQL Server database will cause an out-or-range error for both SQL DateTime and SmallDateTime data types.
• Decimal: can pass to several data types, depending on the need. Using this type presumes a high degree of a priori knowledge about the values to be stored. If this is not the case, consider using a more general-purpose single-to-real or double-to-float data type instead.
  The decimal(p,s) data type allows precision and scale of a decimal number to be specified. The storage size varies depending on the precision and scale values chosen.
  The money data type holds values from (â€"2^63/10000) (â€"922,337,203,685,477.5808) through (2^63/10000)â€"1 (922,337,203,685,477.5807), with accuracy to 1/10000 of a monetary unit. Storage size is 8 bytes.
  The numeric(p,s) data type holds fixed-precision and scale-numeric data from â€"10^38+1 through 10^38â€"1. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p. Storage size is 19 bytes.
  The smallmoney data type holds values from (â€"2^31/10000) (â€"214,748.3648) through (2^31/10000)â€"1 (214,748.3647), with accuracy to 1/10000 of a monetary unit. Storage size is 4 bytes.
• Double: pass to a float (double-precision floating-point number).
• guid: pass to a uniqueidentifier.
• Int16: pass to a SmallInt.
• Int32: pass to a Int.
• Int64: pass to a Bigint.
• Single: pass to a real (single-precision floating-point number).
• Object: pass to an sql_variant.
• Unsigned: there is no SQL Server support for Unsigned integers, except for Unsigned char, which can be stored as a tinyint. Larger Unsigned values may be stored in binary(n) fields.
  For Unsigned Char or UInt8, use tinyint or binary(1).
  For Unsigned Short or UInt16, use binary(2).
  For Unsigned Int or UInt32, use binary(4).
  For Unsigned Long or UInt64, use binary(8).

For more information, start in SQL Server help at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/89b43ee9-b9ad-4281-a4bf-c7c8d116daa2.htm