dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] Stored Procedure...Something, Something

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Resolved [RESOLVED] Stored Procedure...Something, Something

    I have a stored procedure that has this line:
    Code:
    SELECT @Result = (SELECT Top(1) STRM_Survey.LLID FROM STRM_Survey INNER JOIN STRM_Survey_Waypoints ON STRM_Survey.SurveyID = STRM_Survey_Waypoints.SurveyID WHERE Latitude IS NOT Null AND Longitude IS NOT Null  AND STRM_Survey_Waypoints.Latitude = Latitude AND STRM_Survey_Waypoints.Longitude = Longitude)
    It takes two arguments, Latitude and Longitude, and is looking for the first record where there is a match in STRM_Survey_Waypoints for both lat and long. There IS a match with the data I am supplying, and I know the LLID I should be getting back, but that's NOT the LLID that the query returns. In fact, it just returns the first LLID that I would get if the WHERE clause is completely removed.

    In other words, the query is acting as if the WHERE clause was not there at all.

    So, I tried a query where I removed the Latitude and Longitude arguments and just put the actual values directly into the query. That worked as expected, and the right LLID was returned. However, I had dropped the two checks to see whether the arguments are not Null. Adding them back in had no impact.

    Therefore, the situation stands that when I run that query as an SP, the query behaves exactly as it would if the WHERE clause was missing. If I turn that into a query that is not an SP, and paste the values supplied to the arguments into the query, it runs correctly.

    What is going on?
    My usual boring signature: Nothing

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,293

    Re: Stored Procedure...Something, Something

    Code:
    SELECT @Result = (SELECT Top(1) STRM_Survey.LLID FROM STRM_Survey INNER JOIN STRM_Survey_Waypoints ON STRM_Survey.SurveyID = STRM_Survey_Waypoints.SurveyID WHERE Latitude IS NOT Null AND Longitude IS NOT Null  AND STRM_Survey_Waypoints.Latitude = Latitude AND STRM_Survey_Waypoints.Longitude = Longitude)

    This is why I use table aliases and include the aliases on all fields when writing queries... that way there's no question as to what field belongs to what table.
    The only thing I can think of that that the where condition is ambiguous since Lattitude and Longtitude isn't fully qualified.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,538

    Re: Stored Procedure...Something, Something

    This is why I use table aliases and include the aliases on all fields when writing queries...
    Yeah agreed i would write that query like this

    SELECT @Result = (SELECT Top(1) ss.LLID FROM STRM_Survey ss INNER JOIN STRM_Survey_Waypoints ssw ON ss.SurveyID = ssw.SurveyID WHERE ss.Latitude IS NOT Null AND ss.Longitude IS NOT Null AND ssw.Latitude = ss.Latitude AND ssw.Longitude = ss.Longitude)

    i cant say that would definitely fix your issue but it does make sure that it is looking for the fields in the correct tables
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,843

    Re: Stored Procedure...Something, Something

    I've found MS SQL to have issues at times with sub queries. For example, you can have a totally wrong field referenced in a WHERE clause and the SPROC will run fine. You won't get a proper result, but the SPROC completes.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: Stored Procedure...Something, Something

    I guess I had been staring at things too long. These replies showed me exactly what the flaw was. After all, when NSA attempted to clarify the WHERE clause, which may be what TG was also referring to, he did it wrong, which showed me that I had also written it wrong. Where I had just Latitude and Longitude, those were arguments to the SPROC, not fields in a table. However, they ARE fields in one of the tables, and not ambiguous fields, either. They are ONLY found in the one table, so, since I left out the @, what I essentially wrote was:

    WHERE A IS NOT NULL AND B IS NOT NULL AND A = A AND B = B

    Of course, that would return the first record, which is what it was doing. Of course, szlamany knocked the dust loose. I was replying to that post when I realized what I had done.
    My usual boring signature: Nothing

  6. #6
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,412

    Re: [RESOLVED] Stored Procedure...Something, Something

    Btw, this

    SELECT @Result = (SELECT Top(1) STRM_Survey.LLID FROM STRM_Survey. . .

    is equivalent to this

    SELECT TOP(1) @Result = STRM_Survey.LLID FROM STRM_Survey. . .

    and you can extract more values from STRM_Survey in one go.

    cheers,
    </wqw>

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,260

    Re: [RESOLVED] Stored Procedure...Something, Something

    Also, shouldn't you have an order by in there? Top 1 is pretty unpredictable without one.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: [RESOLVED] Stored Procedure...Something, Something

    Don't care about more values. Technically, there could be more than one, but in practice it would be vanishingly unlikely, and it wouldn't matter anyways, as the value returned will be the same for all of them. Those are Lat/Long values obtained from a GPS. While it is possible to have two identical waypoints, we have never done so, in practice.

    This whole question arose because I was using a different part of the query (which I didn't show) that turned out to throw an exception if the waypoints were identical (darn ACOS). That never had an issue the way I had been using the SPROC, because the waypoints have never been identical (and it's virtually impossible that they would be, in this case). However, I then used the SPROC as part of a process where there was guaranteed to be a match, so I needed to check to see if there was a perfect match before I went on with the rest of the method.

    However, as I write this, I realize that I created a tautology. The SPROC works better for its intended purpose (since it will do the right thing in the absurdly unlikely case of a perfect match), and not at all for the alternate purpose I was trying to use it for. Can't have it both ways, either, so I guess I have to make a new SPROC.
    My usual boring signature: Nothing

  9. #9
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,538

    Re: [RESOLVED] Stored Procedure...Something, Something

    After all, when NSA attempted to clarify the WHERE clause, which may be what TG was also referring to, he did it wrong, which showed me that I had also written it wrong
    I did wonder if they were supposed to be variables but just kind of assumed that you hadn't made that mistake
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  10. #10

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,881

    Re: [RESOLVED] Stored Procedure...Something, Something

    That's the thing about mistakes like that: You never made them intentionally, so it's ALWAYS an oversight.

    Didn't help me in match classes, either.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width