Solved

Sorting the result of a stored procedure.

Posted on 2011-03-01
8
648 Views
Last Modified: 2012-05-11
Hiya,
I have a stored procedure that returns a recordset that is used in various ASP forms.

Depending on where I am displaying the results I need to sort them differently.

My current SQL Statement in ASP/VBscript looks like this:

SQLStmt = "exec dbo.usp_CurrentWasteOrderRecords " & v_siteid & ";"

Is there a way of adding and ORDER BY statement to this. If not, how do I go about Sorting the records outside the stored procedure?
0
Comment
Question by:splanton
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 35007686
You can put the results into a #temp table end then query the #temp table with an Order By.

CREATE TABLE #tmp(
[id] int,
name varchar(64),
[my other fields...]
)

INSERT INTO #tmp
exec dbo.usp_CurrentWasteOrderRecords ...


SELECT *
FROM #tmp
ORDER BY ID, ...


--Clean up
DROP TABLE #tmp
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 35007911
you can also try put the order by as your parameters and pass them into your stored procedure, like:

orderBy = "myfield desc"

SQLStmt = "exec dbo.usp_CurrentWasteOrderRecords " & v_siteid & " ", '" & orderBy & "' ;"


Then in your store procedure, construct the select statement and execute it using EXEC command.


hope this helps
0
 
LVL 10

Expert Comment

by:dwe761
ID: 35007960
Keep in mind that my suggested approach makes these assumptions:
1) You do not have the ability to update the original stored proc usp_CurrentWasteOrderRecords, and
2) usp_CurrentWasteOrderRecords only returns one set of results (i.e. one select statement)

For a more complete discussion on the options, try these links:
http://stackoverflow.com/questions/149380/dynamic-sorting-within-sql-stored-procedures

http://dbaspot.com/forums/sqlserver-server/206395-sorting-results-sp-commands.html
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:splanton
ID: 35008518
Hi dwe761, This seems to work fine in SQl but does not seem to work as a command to populate a record set from ASp/VBscript. e.g.:

SQLStmt = "CREATE TABLE #tmp(WasteOrderHeaderId INT,WasteOrderDetailId INT,OrderRef CHAR(10),ContainerDesc varchar(40),WasteTypeDesc varchar(40),ContainerSizeDesc varchar(40),WasteOrderStatusDesc varchar(40),Active BIT) INSERT INTO #tmp exec dbo.usp_CurrentWasteOrderRecord " & parm01 & " SELECT * FROM #tmp ORDER BY " & v_orderby & " " & v_desc & ";"

Set RS_WasteOrder01 = Connection01.Execute(SQLStmt)

It doesn't populate the record set with anything but works fine in the SQL MS :(
0
 
LVL 2

Author Comment

by:splanton
ID: 35008551
Hi ryancys,
Sort of went down this route myself and found that you cannot use a variable '@myvar' in an order by clause. Got stuffed at this point :(
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 35008900
A number of questions come to mind by your last comments...
1) If you are populating a recordset, couldn't you sort the recordset?  (The down side is the SQL Server is no longer doing the heavy lifting if the recordset is large).
2) Is your column(s) used in the order by not fixed? That's an additional consideration if this is the case.

3) Are you able to create new stored procs on your SQL Server?
If so, you could use my suggested approach as a second stored proc which would be a wrapper around yours.  Then you'd pass it your normal parameter(s) but in addition, pass your field name(s) to be used in the order by clause.
Then when you call the stored proc from your code, you'd use the name of the wrapper stored proc as follows:
MyOrderBy = "'ID, LastName'"
SQLStmt = "exec dbo.usp_CurrentWasteOrderRecords_Sorted " & v_siteid & ", MyOrderBy ;"

CREATE PROC usp_CurrentWasteOrderRecords_Sorted( 
	@v_siteid int,
	@sOrderBy varchar(100)
)
AS

CREATE TABLE #tmp(
[id] int,
name varchar(64),
[my other fields...]
)

INSERT INTO #tmp
exec  dbo.usp_CurrentWasteOrderRecords @v_siteid

exec ('SELECT * FROM #tmp ORDER BY ' + @sOrderBy)

Open in new window

0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 35009106
>>you cannot use a variable '@myvar' in an order by clause.

post your SP usp_CurrentWasteOrderRecords here if necessary.

basically make sure you concatenate the string correctly in your SP, without including @myvar as part of the string itself.
0
 
LVL 1

Expert Comment

by:jeff77tor
ID: 35009160
The reason Execute doesn't work is that you are trying to send more than one command.

You can use the ExecuteBatch method of your DataAdapter to run what is essentially a batch of two comments (if you are using ADO.NET

Because your code makes reference to a connection, I'm thinking you are likely using a legacy version of ADO. Unfortunately I don't have a reference handy, but I'm positive there is a way to run a batch with pre .Net ADO.

Another option... Create a new SP that takes the same parameters; creates the temp table and executes the select. You will need to build the SELECT statement as a string (inside the SP), and execute it using sp_executesql statement.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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