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?
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.