Solved

Stored Procedure vs SQL Text

Posted on 1998-12-10
4
258 Views
Last Modified: 2010-03-19
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
Comment
Question by:PBall
  • 2
4 Comments
 

Expert Comment

by:mhamra
ID: 1092196
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
 
LVL 6

Author Comment

by:PBall
ID: 1092197
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
 

Accepted Solution

by:
fduah earned 30 total points
ID: 1092198
try changing the datatype of the paramaters of the stored procedure to datetime. make sure the startdatetime field  datatype is not  "smalldatetime"
0
 
LVL 6

Author Comment

by:PBall
ID: 1092199
That does it. :)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question