Why am I getting a error when passing an integer to a decimal in my stored procedure?

Why am I getting a error when calling my stored procedure?

this fails:

exec dbo.foo 100

but th开发者_开发技巧is works:

exec dbo.foo 99

definition:

CREATE PROCEDURE dbo.foo
(
   @latitude DECIMAL (16,14)
)
AS
BEGIN
    PRINT 'OK'
END

error message:

Msg 8114, Level 16, State 1, Procedure foo, Line 0
Error converting data type int to decimal.

decimal (16,14) means "16 digits, 14 after decimal point". This means 2 before the decimal point. "100" is out of range because it is 3 numbers...

Quick example

DECLARE @fail decimal(16, 14), @pass decimal(17, 14)

BEGIN TRY
    SET @pass = 100
END TRY
BEGIN CATCH
   PRINT 'Will not see this'
END CATCH

BEGIN TRY
    SET @fail = 100
END TRY
BEGIN CATCH
   PRINT 'Will see this'
END CATCH

I don't see the difference between the code that works with the one that fails, but both of them should fail. The first parameter on the DECIMAL data type is the max number of digits that your number can have; in your case its 16. The second parameter is the number of digits reserved for the numbers at the right of the decimal points. So you can't have 100 as a DECIMAL(16,14), just 99.99999999999999.