Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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.
0
PBall
Asked:
PBall
  • 2
1 Solution
 
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
 
fduahCommented:
try changing the datatype of the paramaters of the stored procedure to datetime. make sure the startdatetime field  datatype is not  "smalldatetime"
0
 
PBallAuthor Commented:
That does it. :)
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now