[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

SQL SERVER DYNAMIC ORDER BY WITH ASC OR DESC

I need to make a stored by procedure

CREATE   procedure emp_count

@ORDER AS VARCHAR(1)

AS

SELECT STORE.STORE_NAME, EMPLOYEE.STORE_CODE, COUNT(EMPLOYEE.STORE_CODE) AS EMP_COUNT
FROM STORE
INNER JOIN EMPLOYEE ON STORE.STORE_CODE = EMPLOYEE.STORE_CODE
GROUP BY STORE.STORE_NAME, EMPLOYEE.STORE_CODE

GO

Now if I execute the stored procedure with the parameter of:

'N' I need it to ORDER BY STORE.STORE_NAME ASC

OR

'C' I need it to ORDER BY COUNT(EMPLOYEE.STORE_CODE) DESC

I would I accomplish this?
0
microcoop
Asked:
microcoop
2 Solutions
 
HilaireCommented:
Please give it a try

CREATE   procedure emp_count
@ORDER AS VARCHAR(1)
AS
SELECT STORE.STORE_NAME, EMPLOYEE.STORE_CODE, COUNT(EMPLOYEE.STORE_CODE) AS EMP_COUNT
FROM STORE
INNER JOIN EMPLOYEE ON STORE.STORE_CODE = EMPLOYEE.STORE_CODE
GROUP BY STORE.STORE_NAME, EMPLOYEE.STORE_CODE
ORDER BY CASE WHEN @ORDER = 'N' THEN STORE.STORE_NAME ELSE '' END ASC,
CASE WHEN  @ORDER = 'C' THEN COUNT(EMPLOYEE.STORE_CODE) ELSE 0 END DESC
GO
0
 
izblankCommented:
SELECT STORE.STORE_NAME, EMPLOYEE.STORE_CODE, COUNT(EMPLOYEE.STORE_CODE) AS EMP_COUNT
FROM STORE
INNER JOIN EMPLOYEE ON STORE.STORE_CODE = EMPLOYEE.STORE_CODE
GROUP BY STORE.STORE_NAME, EMPLOYEE.STORE_CODE
ORDER BY CASE @ORDER WHEN 'N' THEN STORE.STORE_NAME ELSE NULL END ASC,
          CASE @ORDER WHEN 'C' THEN COUNT(EMPLOYEE.STORE_CODE) ELSE NULL END DESC
0
 
microcoopAuthor Commented:
They came in so close together I felt like I needed to split them, both answers worked. Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now