SQL - ORDER BY in IF-ELSE CLAUSE

I have ONE table A and if the parameters @x is 0 i want to ordering A by date, else by Name. This is an example:

declar开发者_运维知识库e @x int set @x = 0
if(@x=0)(
SELECT *  FROM A
order by DATE
)
else
(
SELECT *  FROM A
order by Name
)

When try to do it SQL Server return 2 egual error as

Incorrect syntax near the keyword 'order'.

What could i do?

Try this instead

SELECT * FROM A
order by 
Case @x When 0 Then Cast(DATE as sql_variant) Else Name End

The casting is required for the dynamic sort to work with a date - I'm trying to find a reference, but you need a type that can be casted automatically to a common type, and date can't be done. Hence casting yourself.

Here's a link to some options. Note the section on mixed datatypes.

https://web.archive.org/web/20211029044050/https://www.4guysfromrolla.com/webtech/010704-1.shtml

declare @x int set @x = 0 
if(@x=0)
begin 
 SELECT *  FROM A 
 order by DATE 
end
else 
begin
 SELECT *  FROM A 
 order by Name 
end

SELECT
  *
FROM
  A
ORDER BY
  CASE WHEN @x = 0 THEN Date ELSE Name END

declare @x int 
set @x = 0 
IF (@x=0) 
BEGIN
   SELECT * FROM A order by DATE 
END
ELSE
   SELECT * FROM A order by Name
END

http://msdn.microsoft.com/en-us/library/aa933214(SQL.80).aspx

If your query becomes reasonably complex, then copying it into both sides of an IF/ELSE can becomes rather onerous. You can instead use a CASE expression in your ORDER BY. In this case, you might do:

SELECT * FROM A ORDER BY CASE WHEN @x=0 THEN DATE END, CASE WHEN @x<>0 THEN Name END

This can be expanded up to more conditions/columns. You just need to ensure that each CASE expression is returning a sensible data type.

Parentheses ( and ) are wrong. Use BEGIN and END instead.

DECLARE @x INT 
SET @x = 0

IF (@x = 0) BEGIN
    SELECT * FROM A ORDER BY DATE
END
ELSE BEGIN
    SELECT * FROM A ORDER BY Name
END

Also, in my opinion, DATE as column name is not good idea.

As noted in the answers but not explained.

SQL uses BEGIN - END syntax instead of () or {} for block of commands