Input Parameter/Data Type conflict

[Boyd G]

I created a stored procedure with a single input
parameter - datatype = decimal(14,4)

CREATE PROCEDURE dbo.procGetFeedRate
(@paramInputFishLBS int )
AS
SET NOCOUNT ON
SELECT convRatio, pctBdyWtFed
FROM dbo.FeedRates
WHERE (@paramInputFishLBS >= minLBS) AND
(@paramInputFishLBS <= maxLBS)

The maxLBS and minLBS columns that follow the WHERE
clause are also datatype = decimal(14,4)

When I execute the stored procedure from Enterprise
Manager it returns a record set. When the stored
procedure is run from Access2002 it does not return a
record set.

As a work around I converted the minLBS and MaxLBs to
datatype = integer. I also converted the
@paramInputFishLBS to datatype integer. The stored
procedure worked within Access 2002.

Does anyone know if Access2002 has some sort of bug where
you can not pass a decimal datatype as an input parameter
to a stored procedure?

 

[Tom Ellison]

Dear Boyd:

According to your post, the input parameter paramInputFishLBS was NOT
a decimal(14, 4) but was declared within the SP as an int. Did you
have it as:

CREATE PROCEDURE dbo.procGetFeedRate
(@paramInputFishLBS decimal(14, 4) )
AS
SET NOCOUNT ON
SELECT convRatio, pctBdyWtFed
FROM dbo.FeedRates
WHERE (@paramInputFishLBS >= minLBS) AND
(@paramInputFishLBS <= maxLBS)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Boyd]

Yes, I did have the original input query as datatype
decimal (14,4). I started the stored procedure within
Access. When prompted for the input parameter I entered
the number 0.06 which produced an empty record set. I
have double checked the stored procedure input datatype
since my post and the datatypes of minLBS and maxLBS (all
are decimal (14,4)). The only way I can get this SP to
work withing Access is to change the input variable and
minLBS/maxLBS columns to datatype integer.

The stored procedure works fine from Query Analyzer using
the decimal datatype for the input parameter.

One thing is interesting. While using decimal datatype
as an input parameter, I entered an out of range number
as an input variable (99999999999999). Access gave me
the following error message "Error converting numeric
data type to decimal".

-----Original Message-----
Dear Boyd:

According to your post, the input parameter paramInputFishLBS was NOT
a decimal(14, 4) but was declared within the SP as an int. Did you
have it as:

CREATE PROCEDURE dbo.procGetFeedRate
(@paramInputFishLBS decimal(14, 4) )
AS
SET NOCOUNT ON
SELECT convRatio, pctBdyWtFed
FROM dbo.FeedRates
WHERE (@paramInputFishLBS >= minLBS) AND
(@paramInputFishLBS <= maxLBS)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts