BETWEEN statement within stored procedure

I'm having a weird example in SQL Server. The situation is the following: I have a table with 3 columns:

ID  value1  value2
60FB    14.54   46.05
7066    12.18   41.23
7072    16.18   46.65
710F    12.17   47.65
7126    13.18   49.65
713E    17.18   48.65
7141    12.19   41.33
7144    12.18   47.65
7147    19.18   49.65
7149    18.18   42.65
71A6    17.18   43.65
71AA    14.54   44.05

ID is char, value1 is decimal, value2 is decimal.

If I do the select statement in SQL Server:

SELECT ID, value1, value2 
FROM         Node
where value1 between 12.1 and 12.2
and value2 between 41.2 and 41.5

I get this result which is what I want to have:

ID  v开发者_StackOverflow中文版alue1  value2
7066    12.18   41.23
7141    12.19   41.33

Now I want to pass the between parameters into the stored procedure which looks like this:

create procedure XYZ

@value1min decimal(2,5),
@value2min decimal(2,5),
@value1max decimal(2,5),
@value2max decimal(2,5)

AS
BEGIN

SELECT ID, value1, value2 
FROM         Node
where value1 between @value1min and @value1max
and value2 between @value2min and @value2max

When I run the procedure with the same parameters I get the wrong result which is:

ID  value1  value2
7066    12.18   41.23
710F    12.17   47.65
7141    12.19   41.33
7144    12.18   47.65

I think that problem is in the value1 where all values are fulfilling the condition, but the two aren't fulfilling the condition of value2 range so I don't know why 710F and 7144 are appearing in the results.

Does anyone have any answer on how to resolve this issue?

Thanks, Mark

This is more of an extended comment than an answer:

Please for goodness's sake don't simplify the question so much that

1. the problem is gone
2. the proc is not even valid syntax

Sample table

create table node (ID char(4), value1 decimal(10,4), value2 decimal(10,4))
insert node select
'60FB' ,14.54  , 46.05 union all select
'7066' ,12.18  , 41.23 union all select
'7072' ,16.18  , 46.65 union all select
'710F' ,12.17  , 47.65 union all select
'7126' ,13.18  , 49.65 union all select
'713E' ,17.18  , 48.65 union all select
'7141' ,12.19  , 41.33 union all select
'7144' ,12.18  , 47.65 union all select
'7147' ,19.18  , 49.65 union all select
'7149' ,18.18  , 42.65 union all select
'71A6' ,17.18  , 43.65 union all select
'71AA' ,14.54  , 44.05
GO

Sample proc

create procedure XYZ
@value1min decimal(5,2),   --- << --- note here
@value2min decimal(5,2),
@value1max decimal(5,2),
@value2max decimal(5,2)
AS
SELECT ID, value1, value2 
FROM         Node
where value1 between @value1min and @value1max
and value2 between @value2min and @value2max
GO

The select using fixed values, followed by two execs. Note that the 2nd and 3rd params are swapped

select * from node
where value1 between 12.1 and 12.2
and value2 between 41.2 and 41.5
;
exec xyz 12.1, 12.2, 41.2, 41.5
;
exec xyz 12.1, 41.2, 12.2, 41.5

In every single case, the output is

ID     value1    value2
7066   12.1800   41.2300
7141   12.1900   41.3300

Note

• --- << --- note here If you change the param to decimal(2,5), you get Msg 192, Level 15, State 1, Procedure testme, Line 1. The scale must be less than or equal to the precision.
• if you change it to (7,5), there's no difference; numbers that fit in (5,2) will fit in (7,5).

So where is the problem again?

If I were to guess at all, it will be that you are passing the wrong parameters to the EXEC.

You got your DECIMAL definitions mixed up:

decimal(2,5)

That means: I want DECIMAL, with a total of 2 digits, thereof 5 after the decimal separator.

What you probably mean to define is:

decimal(7,5)

DECIMAL with a total of 7 digits, 5 thereof after the decimal separator (and two before it).

What type are the columns in your table??

From the MSDN documentation for DECIMAL:

decimal[ (p[ ,s] )]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.

p (precision)

The maximum total number of decimal digits that can 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 maximum number of decimal digits that can be stored to the right 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.