Solved

Stored Procedure vs SQL Text

Posted on 1998-12-10
4
262 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 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 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