Stored Procedure vs SQL Text

I have the following statement (same statement) executed as a Stored Procedure call and as a string pass directly to recordset in ASP:

SELECT m.MeetingID, m.Title, m.StartDateTime, m.Duration, f.FacilityName, r.RoomName
FROM Meetings m
INNER JOIN RoomLayout l ON l.RoomLayoutID = m.RoomLayoutID
INNER JOIN Rooms r ON r.RoomID = l.RoomID
INNER JOIN Facilities f ON f.FacilityID = r.FacilityID
WHERE m.OnHoldFlag = 1 AND m.CompletedEntryFlag = 1 AND m.StartDateTime >= '12/10/1998' AND m.StartDateTime < '12/11/1998'
ORDER BY f.FacilityName, r.RoomName, m.StartDateTime

The date range can vary.
I created a stored proc:
getOnHold @startdate varchar(11), @enddate varchar(11)
with the same SQL statement as above.
*change the date literal to the variable.

When executing the stored proc getOnHold,
the StartDateTime order is disregarded by SQL Server.

Thus if I have the following record in entry order:

21  12/10/1998 4:00:00 PM
22  12/10/1998 4:30:00 PM
23  12/10/1998 5:00:00 PM
24  12/10/1998 3:00:00 PM

The result will be displayed as entry, not with record 24 first (StartDateTime order)

What's funny is, when the SQL Statement itself is passed directly to the recordset open method, it will produce the right order (w/ StartDateTime in order, record 24 appears first).

i.e.
Set rs = Server.CreateObject("ADODB.Recordset")

strSQL = "...insert the above SQL statement..."

rs.Open strSQL, "conferenceDSN", adOpenForwardOnly, adLockReadOnly

vs

rs.Open "getOnHold('12/10/1998','12/11/1998')", adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Any thought?  Am I doing something wrong?

I even go as far as eliminating all the INNER JOINs and the other ORDER BY entries (in the stored proc) and leave the StartDateTime only in the ORDER BY clause.

I think at some point, if I do not have a WHERE clause, the data will appear in the right order:

SELECT * FROM Meetings ORDER BY StartDateTime

vs.

SELECT * FROM Meetings WHERE StartDateTime >= '12/10/1998' ORDER BY StartDateTime

Weird.
LVL 6
PBallAsked:
Who is Participating?
 
fduahConnect With a Mentor Commented:
try changing the datatype of the paramaters of the stored procedure to datetime. make sure the startdatetime field  datatype is not  "smalldatetime"
0
 
mhamraCommented:
What happens when you execute both methods in ISQL? Do you obtain different results?

What's the type of StartDateTime field ?

Did you check that f.FacilityName and r.RoomName fields are not affecting your result order? Why are you ordering through those fields if you only want StartDateTime order?
0
 
PBallAuthor Commented:
Hehe, yes, but from interdev query tool.

StartDateTime...take a guess :)  datetime of course.

Disregard the facilities and rooms if you will, it will still bear the "screwed up" resultset.

Is it not obvious ...hmm..damn shift keys are acting up on me...(question mark).  It's because I want the recordset to be displayed in those order (facility first ascending, room name second ascending, and meeting start date and time ascending).

And yes, I tested the query with and without the inner joins and the offending order clause.  I DO know how to use SQL and not THAT stupid. hehehe.


0
 
PBallAuthor Commented:
That does it. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.