ORER BY in an IF ? 1

[irate]

The following code in the query analyzer gives me an error:

Server: Msg 156, Level 15, State 1, Procedure searchDynamicContent, Line 18
Incorrect syntax near the keyword 'ORDER'.

Can someone tell me what I am doing wrong please?

CREATE PROCEDURE searchDynamicContent

		@dynamicArea VARCHAR(25),
		@dynamicTitle VARCHAR(50),
		@dynamicOrder int
AS

SELECT *

FROM		dynamicContent

WHERE		dynamicContentArea = @dynamicArea
	AND	dynamicContentTitle LIKE '%' + @dynamicTitle + '%'
	AND	dynamicContentStatus != 'DELETED'

if (@dynamicOrder = 1)
	begin
   		ORDER BY dynamicContentTitle ASC
	end

GO

 

[swampBoogie]

CREATE PROCEDURE searchDynamicContent

        @dynamicArea VARCHAR(25),
        @dynamicTitle VARCHAR(50),
        @dynamicOrder int
AS
if (@dynamicOrder = 1)
SELECT *
FROM        dynamicContent
WHERE        dynamicContentArea = @dynamicArea
    AND    dynamicContentTitle LIKE '%' + @dynamicTitle + '%'
    AND    dynamicContentStatus != 'DELETED'
           ORDER BY dynamicContentTitle ASC
else
SELECT *
FROM        dynamicContent
WHERE        dynamicContentArea = @dynamicArea
    AND    dynamicContentTitle LIKE '%' + @dynamicTitle + '%'
    AND    dynamicContentStatus != 'DELETED'

 

[SQLSister]

irate, you can't use an If statement from within a select statement. SwampBoogie's code should work however.

 

[zhavic]

Also can be done with

SELECT *
FROM dynamicContent
WHERE dynamicContentArea = @dynamicArea
AND dynamicContentTitle LIKE '%' + @dynamicTitle + '%'
AND dynamicContentStatus != 'DELETED'
ORDER BY CASE WHEN @dynamicOrder = 1 THEN dynamicContentTitle ELSE 'blahblah' END ASC

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.