Dynamic Order By with CASE Statement 1

[tshad]

I am trying to decide whether to set up multiple queries specifically for different order by situations or to use an Order By.

How would I change the following to use one query with an Order By (without using dynamic SQL) and CASE Statement?

And if you can would that be better?

If OBJECT_ID('tempdb..#Stage') IS NOT NULL
	DROP TABLE #Stage


CREATE TABLE #Stage
(
	StageID int,
	ShipNumber varchar(10),
	PONumber varchar(10),
	iQty int
)

DECLARE @OrderBy int
SELECT @OrderBy = 3

INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (20, 'MZ12345', 'A4735', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (22, 'MZ12345', 'A4735', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (23, 'MZ12345', 'A4735', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (35, 'MZ12345', 'J3321', 40)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (36, 'MZ12345', 'J3321', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (32, 'MZ12345', 'V3199', 10)

IF @OrderBy = 1
BEGIN
	SELECT * FROM #Stage
	ORDER BY ShipNumber
END
IF @OrderBy = 2
BEGIN
	SELECT * FROM #Stage
	ORDER BY PONumber, ShipNumber, iQty
END
IF @OrderBy = 3
BEGIN
	SELECT * FROM #Stage
	ORDER BY iQty, PONumber
END

Thanks,

Tom

 

[gmmastros]

This should work:

Order By Case When @OrderBy = 1 Then ShipNumber
              When @OrderBy = 2 Then PONumber
              When @OrderBy = 3 Then Right('0000000000' + Convert(VarChar(10), iQty), 10)
              End,
         Case When @OrderBy = 2 Then ShipNumber
              When @OrderBy = 3 Then PONumber
              End,
         Case When @OrderBy = 2 Then iQty End

Notice this part:

[tt]When @OrderBy = 3 Then Right('0000000000' + Convert(VarChar(10), iQty), 10)[/tt]

When you use case/when, it's important that each branch of the case statement returns the same data type. If they don't return the same data type, SQL Server will automatically convert it to the same data type depending on the branches of the case statement. When mixing int and string, SQL Server will automatically convert it to integer. Since your data cannot be converted to integer, you would get an error.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry. I missed the 2nd part of your question.

And if you can would that be better?

If this query usually returns a small number of rows, then I would use the case statement in the order by clause. With the case statement in the order by clause, SQL Server will need to sort the output instead of using an index. With a small number of rows, the sort operation would be quick. With a large number of rows, it would be better to stick with the IF statements you currently have.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

How would you change it to be no-orderby?

For instance, I could set the @OrderBy to be:

@OrderBy int = NULL

to allow me to not use the Order By clause at all.

Is that possible in this scenario?

Thanks,

Tom

 

[gmmastros]

If @OrderBy is anything other than 1, 2 or 3, there would not be an order by. Please note that eventhough there is no order by, the data may appear to be ordered. If that's the case, it would be coincidental. You see, if SQL Server decides to use a particular index to speed up the query, and there is no order by, then SQL Server will not sort the data, but because of the index used to execute the query, the data may appear to be sorted.

Bottom line... if you don't specify an order by, the order is not guaranteed.

In the example I gave earlier, the CASE expressions do not have an ELSE clause. If @OrderBY is null or a number other that 1, 2, or 3, then the CASE expressions would return NULL, effectively giving you an order by of:

Order By NULL, NULL, NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom