CODEWITHSUNDEEP
c#sqlsql-servert-sql
Scott Selby
Scott Selby

Reputation: 9570

SQL Value out of range

I have a stored procedure with parameters like so:

@longitude decimal(5, 5),
@latitude decimal(5, 5),
@radius decimal(2, 2),
@DayOfWeek int

I pass in -87.66826 for longitude and I get

Parameter value '-87.66826' is out of range.

Am I missing something stupid here??

Upvotes: 1

Views: 1224

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Simple..

Change like this

@longitude decimal(7,5),
@latitude decimal(7,5),
@radius decimal(2,2),
@DayOfWeek int

decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )]

According to decimal and numeric (Transact-SQL):

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)

The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Upvotes: 1

Farhad Jabiyev
Farhad Jabiyev

Reputation: 26645

Change @longitude decimal(5,5) to @longitude decimal(7,5). Also change other decimal parameter types also.

decimal(5,5) means 0 digit before the decimal point and 5 digits after the decimal point. But you need 2 digits before and 5 digits after the decimal point. So total length is 7.

Also these types is recommended for storing langitude and longitude in Sql Sevrer 2008. You could read this article for using these types.

You should take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are specifically designed this kind of task and make indexing and querying the data much easier and more efficient.

Upvotes: 4

dima
dima

Reputation: 1189

Decimal(5,5) means 5 total digits and 5 behind the decimal therefore it means zero digits before the decimal.

Also, depends on what SQL Server you use, there is also geometry data type that can store longitude and latitude

Upvotes: 2

Related Questions