Solved

Sorting the result of a stored procedure.

Posted on 2011-03-01
8
640 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
  • 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 49

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
 
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
Backup Your Microsoft Windows Server®

Backup 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.

 
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 49

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 46
join tables 4 51
How to send multiple emails at the same time in PHP 12 29
updating the date data 12 25
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now