Re: Regarding Order By Clause In Stored Procedure

James Simpson wrote:
Hello,
I am using ADO in MFC along with SQL Server 2005 Express to handle
the data that my application will be using. Everything is going well
so far, except when I try to run a stored procedure that is supposed
to give me the highest ID from a table (the ID being with properties
of it being an integer,primary key, identity value (increment by 1 on
each row insert). Here is the code of the stored procedure :


Here is the problem that I have : If I tell it to order by
[WorksheetID] DESC; and the use a recordset to go through it, I get
the lowest ID value first (from WorksheetID), however when I tell it
to order [WorksheetID] ASC; SQL properly gives me the highest ID
value first and then goes downward to lower IDs. What gives? Isn't
DESC on Group By supposed to give you the highest value first and
then go downwards?

What is the purpose of this sproc? Cycling through a recordset to find the
lowest or highest ID value does not seem like the most efficient use of
resources ...
I guess I will have to reformat that thing so I can read it and figure out
what it's doing ...

CREATE PROCEDURE [dbo].[sproc_selectwork***]
@SectionID As Integer Output,
@IsPreshiftIncluded As Integer Output,
@NumberOfTests As Integer Output,
@EnableCollaboration As Integer Output,
@PlantName As varchar(100) Output,
@PlantPassword as varchar(100) Output,
@CollaborateAddress as varchar(256) output,
@PrintDocumentInFullColour as bit output,
@SetRowsAsDays As varchar(256) output,
@StartDate As Integer Output,
@WorksheetID As Integer Output
AS
Select @WorksheetID=[WorksheetID]
,@SectionID=[SectionID], @IsPreshiftIncluded=[Is Preshift
Included],@NumberOfTests=[Number of Tests],
@EnableCollaboration=[Enable Collaboration], @PlantName=[Plant Name],
@PlantPassword=[Plant Password], @CollaborateAddress = [Collaborate
Address], @PrintDocumentInFullColour=[Print Document In Full
Colour],@SetRowsAsDays=[Set Rows As Days], @StartDate=[Start Date]
FROM tblWork***
ORDER BY [WorksheetID] DESC;

Recordset? What recordset? This procedure does not return a resultset ...
oh! You converted it to a simple select so you could see what was going on?

OK, the first problem here is you are using a sql statement that retrieves
more than one row to assign values to scalar variables. What is happening is
that the select statement is giving you the values of the last row in your
resultset, not the first. Since you used DESC, the last row contains the
lowest ID. Clear?

You need to rewrite it so it retrieves a single row, either via the TOP
keyword, or via a grouping subquery. It seems like your goal is to retrieve
the values of all these columns from the row where the worksheetid column
contains the highest value, right? TOP 1 might work, but personally, I would
use WHERE clause with a subquery (why make the query engine go to all the
trouble of sorting all the rows in the table when you really want the values
from a single row?). Try this:

Select @WorksheetID=[WorksheetID]
,@SectionID=[SectionID], @IsPreshiftIncluded=[Is Preshift
Included],@NumberOfTests=[Number of Tests],
@EnableCollaboration=[Enable Collaboration], @PlantName=[Plant Name],
@PlantPassword=[Plant Password], @CollaborateAddress = [Collaborate
Address], @PrintDocumentInFullColour=[Print Document In Full
Colour],@SetRowsAsDays=[Set Rows As Days], @StartDate=[Start Date]
FROM tblWork***
WHERE WorksheetID = (
SELECT Max(WorksheetID)
FROM tblWork***)

PS. I applaud your use of output variables vs. using a simple SELECT
statement to return a single row of data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.