Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sorting the result of a stored procedure.

Posted on 2011-03-01
8
Medium Priority
?
656 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 54

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 2000 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 54

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

782 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