Implement Dynamic Order

[Zecho]

I am working on a table database related to manufacturing of certain products related to my company. I want to implement the order by in my sql large table database. I need some explanation with examples to implement the dynamic order in table with multiple column. Also how it works and what feed back can I get from this. In short I want to create for the select statements. Thanks.

[Reegan]

The points you will need to consider while building up a dynamic order is first define all the related query in advance. Then while building sql sort the forms and queries in XSD. Build the query suing Exec in the store procedure to run it. Try to sort it problematically. See the below example you will get an general idea about the structure.

CREATE PROCEDURE MyProc
   @SortType int
AS
BEGIN
   
   SELECT
      MyTable.FieldA,
      MyTable.FieldB
   FROM
      MyTable
   ORDER BY
      CASE 
        WHEN @SortType = 1 THEN FieldA
        WHEN @SortType = 2 THEN FieldB
        ELSE FieldA
      END
   
END

[MindSpace]

Here is an sample of Dynamic ORDER By. The example depends on a example for the reference and can be used to implement ORDER BY. I will assume that you want to sort the result of a particular query in a particular column referenced in the SELECT.

CREATE PROCEDURE ps_Customers_SELECT_DynamicOrderBy
@SortOrder tinyint = NULL
AS
SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
              ELSE ContactTitle
         END

Here you can call EXEC ps_Customers_SELECT_DynamicOrderBy, EXEC ps_Customers_SELECT_DynamicOrderBy 1 and EXEC ps_Customers_SELECT_DynamicOrderBy 2.

[Modifier]

I am listing a example code by which you can used COALESCE and CASE to create a store procedure that usually supports both dynamic WHERE and ORDER BY. There are many examples on the net also. If possible refer them also to get more information. Refer the below code.

CREATE PROCEDURE ps_Player_SELECT_ByStats (
@OrderBy tinyint = 1,
@Pla_BattingAverage smallint = NULL,
@Pla_HomeRuns smallint = NULL,
@Pla_RBIs smallint = NULL,
@Pla_StolenBases smallint = NULL,
@RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

SELECT Pla_FName+' '+Pla_LName AS Name,
       Pla_BattingAverage,
       Pla_HomeRuns,
       Pla_RBIs,
       Pla_StolenBases
FROM Players
WHERE Pla_BattingAverage >= COALESCE(@Pla_BattingAverage,0) AND
      Pla_HomeRuns >= COALESCE(@Pla_HomeRuns,0) AND
      Pla_RBIs >= COALESCE(@Pla_RBIs,0) AND
      Pla_StolenBases >= COALESCE(@Pla_StolenBases,0)
ORDER BY CASE WHEN @OrderBY = 1 THEN Pla_BattingAverage
              WHEN @OrderBY = 2 THEN Pla_HomeRuns
              WHEN @OrderBY = 3 THEN Pla_RBIs
              WHEN @OrderBY = 4 THEN Pla_StolenBases
              END DESC

SET @RowsReturned = @@ROWCOUNT