Why is this SQL not working as a stored procedure, but works fine as a regular query?

Question

The TrackingData table is inside a database named Tracking. The stored procedure is being run inside the same database. I get data back with the query, but not with the SP.

SELECT *
    FROM
      dbo.TrackingData
      LEFT OUTER JOIN SSMain.dbo.EmailCampaignTracking ON (dbo.TrackingData.emailCampaginTrackingID = SShMain.dbo.EmailCampaignTracking.emailCampaignTrackingID)
      LEFT OUTER JOIN SSMain.dbo.EmailCampaigns ON (SSMain.dbo.EmailCampaignTracking.emailCampaignID = SSMain.dbo.EmailCampaigns.emailCampaignID)
      LEFT OUTER JOIN SpinStitchMain.dbo.EmailListAddresses ON (SSMain.dbo.EmailCampaignTracking.emailAddressID = SSMain.dbo.EmailListAddresses.emailAddressID)
    WHERE
      dbo.TrackingData.lattitude = 33.8322 AND 
      dbo.TrackingData.longitude =  -78.6491 and
      dbo.TrackingData.projectID = 131

CREATE PROCEDURE dbo.sel_Track_HitsByLatLong(
@latitude decimal(18,15),
@longitude decimal(18,15),
@projectID int
)
AS
BEGIN
 SELECT *
FROM
  dbo.TrackingData
  LEFT OUTER JOIN SSMain.dbo.EmailCampaignTracking ON (dbo.TrackingData.emailCampaginTrackingID = SSMain.dbo.EmailCampaignTracking.emailCampaignTrackingID)
  LEFT OUTER JOIN SSMain.dbo.EmailCampaigns ON (SSMain.dbo.EmailCampaignTracking.emailCampaignID = SSMain.dbo.EmailCampaigns.emailCampaignID)
  LEFT OUTER JOIN SSMain.dbo.EmailListAddresses ON (SSMain.dbo.EmailCampaignTracking.emailAddressID = SSMain.dbo.EmailListAddresses.emailAddressID)
WHERE
  dbo.TrackingData.lattitude = @latitude AND 
  dbo.TrackingData.longitude = @longitude and
  dbo.TrackingData.projectID = @projectID
END

Edit:

Turns out the numbers are getting zeros added to the end of them: 33.832200000000000

This has never happened befor an dnot sure what has changed. They get added when the prcedure is run.

Answer 1

i would bet that it has to do with a conversion issue. Is TrackingData.lattitude and TrackingData.longitude really decimal(18,15) in your table?

can you replace the parameters in the SP with the two values in your first query and get the answer back? If so, then it's somewhere in the conversion when you are passing the parameters in.

Answer 2

Do you have access to SQL Server profiler? If so I would recommend catching a trace of what is actually being passed to the execute function as the parameters. Perhaps there is a precision problem with the values being passed to the SP. You think its 131 but you are getting 131.00000000000000000000000000001 instead.

Answer 3

I work with Lat's and Long's all the time - and I used to have DECIMAL(18,15) as the datatype.

I also had this problem :(

For me, it was a LOCALIZATION issue -> when a user from a non en-us/en-gb, etc.. location hit my site, the PERIOD was replaced with a COMMA. so i was trying to pass in 123,111 for a decimal value. fail. This means that, in my .NET application, the current thread's CultureInfo was getting auto set to the locale of the users connection (eg. es for spain, etc).

.
(for a .NET product/project)....

Try making sure u set the thread's cultureinfo to en-gb (that IS proper english, after all .. swipe!) and then seeing if the stored proc now works.

Too me -aaaaaagggggeeeeesssssss- to fix that bug :) u see, it always worked on my local machine (en-au) and as a query ... :)

good luck :)

Answer 4

Just as a FYI, latitude and longitudes having fifteen decimal digits of precision after the decimal is almost certainly excess precision. Near the equator, a precision of six digits corresponds to 11 centimeter (4.3 inch) resolution. Nine more orders of magnitude is well on the way to molecular size precision....

Answer 5

What does this give you outside of the stored proc?

...
WHERE
  dbo.TrackingData.lattitude = CAST(33.8322 as decimal(18,15)) AND 
  dbo.TrackingData.longitude =  CAST(-78.6491 as decimal(18,15)) and
  dbo.TrackingData.projectID = CAST(131 as int)

Then add this the stored proc

SELECT @latitude, @longitude, @projectID

Between this, you should see exactly what the stored proc is working with and what the query returns when datatypes match

Answer 6

OK, so I changed the Lat and Long parameters to type of FLOAT. It works perfect now. I wasn't thinking that when sending the params as decimal(18,15), that it would add zeros to the end if the precision was lass than 15. Thanks to everybody who helped out with this.