Dynamic Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Order By

Author

Topic

dummy-girl
Starting Member

3 Posts

Posted - 2005-05-06 : 17:26:20

Here is the criteria:
1. Need to sort by three columns (different datatypes)
2. Each of the 3 columns will have their own order(asc/desc)
3. The stored procedures are having multiple UNIONs in them.

Can anyone suggest a solution for this? I tried using CASE-WHEN-THEN, but is not working properly with UNION and columns with different datatypes!

Here is how I may want my stored procedure to look like. This is just a sample that I created with only 2 unions and is not working.

CREATE procedure TempProcedure
@Unit	char(2),
@dlrnum char(4),
@dType char(4),
@tranID  int,
@isAdHocSorting char(1),
@sortColumnName1 char(20),
@sortColumnName2 char(20),
@sortColumnName3 char(20),
@sortOrder int
as
set nocount on
BEGIN
	SELECT  B.DailySO, A.Group_Code, A.RefNum, A.InvType, B.SHeader, B.NHRef, A.CustInd, 
		A.TranCode, A.MacDesc, A.SerNum, convert(varchar,A.QtyAvail) As QtyAvail, 
		convert(varchar,A.Amount_Owed) As Amount_Owed, A.STAccnt
	  FROM  Inventory A,Group_Code B
	  WHERE  A.Group_Code=B.Group_Code
	   AND  A.Dealer_Unit_Code = @Unit 
	   AND  A.Dealer_number = @dlrnum
	   AND  A.RefNum NOT IN (SELECT distinct RefNum FROM Settlement_List WHERE Dealer_Unit_Code =@Unit
					   AND Dealer_number = @dlrnum AND tran_id = @tranID)
    UNION
	SELECT  B.DailySO, A.Group_Code, A.RefNum, A.InvType,	B.SHeader, B.NHRef, A.CustInd, 
		C.Settlement_Code, A.MacDesc,A.SerNum, convert(varchar,A.QtyAvail) As QtyAvail,
		convert(varchar,A.Amount_Owed) As Amount_Owed, C.STAccnt
	 FROM   Inventory A,Group_Code B,Settlement_List C,Customer D
	WHERE   A.Group_Code=B.Group_Code
	  AND   A.Dealer_Unit_Code=C.Dealer_Unit_Code
	  AND   A.Dealer_number=C.Dealer_number
	  AND   A.RefNum=C.RefNum
	  AND   A.InvType=C.InvType
	  AND   C.Dealer_Unit_Code=D.Dealer_Unit_Code
	  AND   C.Dealer_number=D.Dealer_number
 	  AND   C.Customer_number=D.Primary_Customer_number
	  AND   D.Customer_number IS NOT NULL
	  AND   A.Dealer_Unit_Code = @Unit
	  AND   A.Dealer_number = @dlrnum
	  AND   C.TRAN_ID = @tranID

 ORDER BY 
   CASE
	WHEN @isAdHocSorting = 'N' THEN TranCode + ',' + RefNum + ',' + QtyAvail
	WHEN @sortColumnName1 = 'RefNum' 	 THEN RefNum 
	WHEN @sortColumnName1 = 'MacDesc' 	 THEN MacDesc 
	WHEN @sortColumnName1 = 'SerNum'  THEN SerNum 
	WHEN @sortColumnName1 = 'QtyAvail' 	 THEN QtyAvail 
	WHEN @sortColumnName1 = 'Amount_Owed' 		 THEN Amount_Owed
	WHEN @sortColumnName1 = 'STAccnt' 	 THEN STAccnt
	ELSE QtyAvail + ',' +  RefNum
    END
END

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-06 : 17:33:21

Use a derived table.



SELECT Column1, Column2
FROM 
(
	SELECT Column1, Column2
	FROM Table1
	UNION ALL
	SELECT Column1, Column2
	FROM Table2
) t
ORDER BY...

Tara

dummy-girl
Starting Member

3 Posts

Posted - 2005-05-06 : 17:37:25

Order By what column name should I mention?!

I want to mention a variable name(actually 3), that the user will supply at runtime!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-06 : 17:44:11

Well Column1 has the data for Column from1 both tables and same with Column2.

And yes you can still reference variable names in the ORDER BY using the example I provided.

Just pretend that the UNION query in my example is now an actual table. That's what a derived table is.

Here's yours rewritten (untested though):



SELECT
	DailySO, Group_Code, RefNum, InvType, SHeader, NHRef, CustInd, 
	TranCode, MacDesc, SerNum, QtyAvail, Amount_Owed, STAccnt
FROM
(
	SELECT  B.DailySO, A.Group_Code, A.RefNum, A.InvType, B.SHeader, B.NHRef, A.CustInd, 
		A.TranCode, A.MacDesc, A.SerNum, convert(varchar,A.QtyAvail) As QtyAvail, 
		convert(varchar,A.Amount_Owed) As Amount_Owed, A.STAccnt
	  FROM  Inventory A,Group_Code B
	  WHERE  A.Group_Code=B.Group_Code
	   AND  A.Dealer_Unit_Code = @Unit 
	   AND  A.Dealer_number = @dlrnum
	   AND  A.RefNum NOT IN (SELECT distinct RefNum FROM Settlement_List WHERE Dealer_Unit_Code =@Unit
					   AND Dealer_number = @dlrnum AND tran_id = @tranID)
    UNION
	SELECT  B.DailySO, A.Group_Code, A.RefNum, A.InvType,	B.SHeader, B.NHRef, A.CustInd, 
		C.Settlement_Code, A.MacDesc,A.SerNum, convert(varchar,A.QtyAvail) As QtyAvail,
		convert(varchar,A.Amount_Owed) As Amount_Owed, C.STAccnt
	 FROM   Inventory A,Group_Code B,Settlement_List C,Customer D
	WHERE   A.Group_Code=B.Group_Code
	  AND   A.Dealer_Unit_Code=C.Dealer_Unit_Code
	  AND   A.Dealer_number=C.Dealer_number
	  AND   A.RefNum=C.RefNum
	  AND   A.InvType=C.InvType
	  AND   C.Dealer_Unit_Code=D.Dealer_Unit_Code
	  AND   C.Dealer_number=D.Dealer_number
 	  AND   C.Customer_number=D.Primary_Customer_number
	  AND   D.Customer_number IS NOT NULL
	  AND   A.Dealer_Unit_Code = @Unit
	  AND   A.Dealer_number = @dlrnum
	  AND   C.TRAN_ID = @tranID
) t
ORDER BY 
	CASE
		WHEN @isAdHocSorting = 'N' THEN TranCode + ',' + RefNum + ',' + QtyAvail
		WHEN @sortColumnName1 = 'RefNum' THEN RefNum 
		WHEN @sortColumnName1 = 'MacDesc' THEN MacDesc 
		WHEN @sortColumnName1 = 'SerNum' THEN SerNum 
		WHEN @sortColumnName1 = 'QtyAvail' THEN QtyAvail 
		WHEN @sortColumnName1 = 'Amount_Owed' THEN Amount_Owed
		WHEN @sortColumnName1 = 'STAccnt' THEN STAccnt
		ELSE QtyAvail + ',' +  RefNum
	END

For the different data type issue, check out the comment section in this article:
http://www.sqlteam.com/item.asp?ItemID=2209

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources