Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sorting the result of a stored procedure.

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

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 53

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

688 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