Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 31-07-2009
Member
 
Join Date: Dec 2008
Posts: 112
How to stored procedure and large table are dynamic order by performance

I have a project work where there was a requirement that, there's a way using MSSQL to dynamically determine what column should be ordered by in a select, This is because the main motive behind creating this is to look into sorting tables by a column designated given some input, and when i search for that on the internet I found that there is no easy way to do this. Does anyone provide me simple way to achieve this.

Thanks
Reply With Quote
  #2  
Old 31-07-2009
Member
 
Join Date: Dec 2008
Posts: 161
Re: How to stored procedure and large table are dynamic order by performance

I think you need to choose the parameter to specify the sort type and also make use of case statement in the order by clause. Here i will provide you the simple coding about how do you need to implement it,

AS
BEGIN

SELECT
TableField.colmnA,
TableField.colmnB
FROM
TableField
ORDER BY
CASE
WHEN @SortType = 1 THEN FieldA
WHEN @SortType = 2 THEN FieldB
ELSE FieldA
END

END
Reply With Quote
  #3  
Old 31-07-2009
Member
 
Join Date: Dec 2008
Posts: 112
Re: How to stored procedure and large table are dynamic order by performance

Your idea of paging the data is a very good one. The part that is bad is using variables for the table names, order by, etc. but I need to be able to change the asc/desc too. I guess that'd reduce it to 2 queries though, which is something, but it'd be a real mess with the number of fields it's on.
Reply With Quote
  #4  
Old 31-07-2009
Member
 
Join Date: Jan 2009
Posts: 140
Re: How to stored procedure and large table are dynamic order by performance

I think you need to use the INSERT INTO and ORDER BY methods to be used in your program to sort the items, and If sorting column is defferent from the PK, then SortColumn must be fetched as well, otherwise just the PK is necessary, and once this has been done then use the Join method to be used. This will combine all the data and sort the table content.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to stored procedure and large table are dynamic order by performance"
Thread Thread Starter Forum Replies Last Post
Stored procedure in MySQL database Ameeryan Software Development 5 04-03-2010 10:31 PM
Use Of Stored Procedure in SQL Server technika Software Development 5 30-01-2010 11:24 AM
What is sqlca Informix stored Procedure Bosch Software Development 3 13-08-2009 01:57 PM
How to create, deploy and use DB2 stored procedure garfield1 Software Development 3 08-08-2009 11:10 AM
How to Write stored procedure in SQL Server Benito Software Development 3 03-08-2009 12:29 PM


All times are GMT +5.5. The time now is 12:39 PM.