Decimals in stored proc parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a stored procedure which will return a list of records with geographic
coordinates (decimal degrees) within a certain range. The parameters it
prompts for are the coordinates of the top left hand corner and the bottom
right-hand corner of a rectangle - 4 parameters of data type decimal(10,6).
If I hard-code the coordinates into the stored proc, evrything works
perfectly. If I prompt for the coordinates, the stored proc ignores the
decimals and uses only the integer portion of the numbers - giving a vastly
different set of records as a result. I originally had this as a user-defined
function and it had exactly the same problem. The code:

CREATE PROCEDURE dbo.GetSpecimenCoords (@DDSParm1 decimal(10,6), @DDEParm1
decimal(10,6), @DDSParm2 decimal(10,6), @DDEParm2 decimal(10,6))
AS SELECT RecordID, RecordType, Date, Locality, DDS, DDE, Datum,
Altitude, Collector, CollectorNumber, TaxonID
FROM dbo.vwSpecimen
WHERE DDS >= @DDSParm2 AND DDS <= @DDSParm1 AND DDE >= @DDEParm1
AND DDE <= @DDEParm2

I'm at my wits end - can anyone suggest anything?
 
Yes -- validate your incoming parameters to make sure your WHERE
clause contains the values you think it does. Also, check the entire
SELECT using PRINT statements to see if it matches the hard-coded
string.

--Mary
 
I have the same problem, running the function directly from Access while
designing, save it and click the ! .
 
Back
Top