Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure vs SQL Text

Posted on 1998-12-10
4
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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