Passing parameter to stored procedure from C#: Which SQLDbType to use for numeric(18, 0)

Question

I have a stored procedure in SQL Server which has an output parameter pf type numeric(18,0). From C# I create a SqlParameter with a SqlDbType.Decimal and I set precision to 18 and scale to 0.

Here is my code:

queryParameters[2] = new SqlParameter("@Id", SqlDbType.Decimal);
queryParameters[2].Precision = 18; // # digits
queryParameters[2].Scale = 0;      // # decimals
queryParameters[2].Direction = ParameterDirection.Output;

The stored procedure is executed correctly, and returns output parameter correctly, but when I parse this into a long variable in C# as below:

long id = (long)queryParameters[2].Value;

it says cannot convert it.

But if I modify the SqlParameter type to SqlDbType.Bigint, then it works.

So it is correct what I am doing by passing it as SqlDbType.BigInt? Or is better to pass it as SqlDbType.Decimal and then use a decimal C# variable instead of long?

Passing it as SqlDbType.Decimal and then doing below is also working:

decimal id = (decimal)queryParameters[2].Value;

So what approach is the best taken into account that this number is an integer without decimals?

Answer 1

If it's defined as numeric(...) in the database, then it's has to be a decimal in C# - whether it (currently) has digits after the comma or not.

Don't mis-represent it as a bigint / long - it's just plain not that type! Also - why are you trying to parse it / convert it into a long in the first place? Makes no sense to me ..... it looks like a decimal, walks like a decimal, quacks like a decimal - then you're pretty sure IT IS a decimal !

What happens if suddenly your SQL Server type changes to numeric(20,2) ? By using a decimal in C# - you're fine, no changes needed. If you used a long instead, now you need to start changing your code to decimal.

Always use the most appropriate type - and for a T-SQL numeric(), that's a decimal in C# / .NET.