Variable as numeric sent to stored procedure (SQL Server 2005)

Question

I see that with SQL Server 2005 you can pass a parameter as numeric e.g.

create procedure dbo.TestSP
 @Param1     numeric
as

But what does this equate to? E.g. Numeric(10,0), Numeric(9,2), etc? We have some Developers here who are using this instead of the correct definition for the field that this parameter is going to be used against e.g. instead of using Numeric(10, 0) for the parameter @Param1.

Also are there any underlying performance issues with using Numeric instead of the data type defined against the field in the table?

Many thanks.

Answer 1

According to the SQL Server Books Online on MSDN, the default precision is 18 and the default scale is 0, so

NUMERIC    is equal to   NUMERIC(18,0)

Answer 2

it defaults to 18,0 when you don't specify scale or precision

Also are there any underlying performance issues with using Numeric instead of the data type defined against the field in the table?

You could get conversions which might slow your query down

Answer 3

Your developers are using a poor programming practice. When defining a variable, always make it match as much as possible the field it is going to be used against and never fail to set the definition correctly or you may get unjexpected truncations of data. This use of numeric is bad enough but they are likely to realize that the decimals got truncated when the check their results. WHen they do this same sloppy programming with varchars they also get truncated values but may not notice them as easily. I would insist in a code review that they fix this junk and stop being lazy and incompetent.

Answer 4

as already said, numeric without numeric(precision,scale) defaults to numeric(18,0)

However, smack those lazy programmers! if they really need 18 digits use bigint!!

otherwise use the datatype of the proper size and precision:

int, bigint, smallint, and tinyint (Transact-SQL)
decimal and numeric (Transact-SQL)
float and real (Transact-SQL)