Solved

Sorting the result of a stored procedure.

Posted on 2011-03-01
8
644 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

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
Not listening to where 1 42
When are cursors useful? 8 60
What does "Between" mean? 6 45
T-SQL: need to reset a declared variable 4 31
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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