Adding Sort direction to current Stored Procedure via parameter

[Ovatvvon]

Below is a stored prodecure that Sunil helped me with (among others) in another forum. It works almost perfectly, except I forgot about adding a sorting-direction.

I added the @sortType variable/parameter that will take in either "ASC" or "DESC". Ideally I wanted to change the lines to just have this parameter at the end of it, so it would read:

...
		Case @SortOrder When 1 Then C.id + @sortType
			When 2 Then C.dba_name + @sortType
			When 3 Then C.phone + @sortType
			When 4 Then C.fax + @sortType
			When 5 Then C.payroll_rep + @sortType
...

However, this does not work. Is there a simple way to make this work without having to re-write everything twice in an If-Else statement checking for the sorting direction value?


Here is the stored procedure:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int,
    @sorttype varchar(4)
AS
IF @status != '0'
Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, 
            TBL.cam, employeeCount
FROM
( SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount, 
		Case @SortOrder When 1 Then C.id
			When 2 Then C.dba_name
			When 3 Then C.phone
			When 4 Then C.fax
			When 5 Then C.payroll_rep
			When 6 Then CP.csr
			When 7 Then CP.cam
			When 8 Then CAST(COUNT(DISTINCT EC.employee_id) as varchar)
		End SortFld,
        @SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
Where	C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND C.phone LIKE @phone AND C.client_status = @status
GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam ) TBL
Order by  case When SortOption=8  then cast(SortFld as int) else 0 end,
	case When SortOption<>8  then SortFld  else '' end 

ELSE

Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, TBL.cam, employeeCount
FROM
(SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount,
		Case @SortOrder When 1 Then C.id
			When 2 Then C.dba_name
			When 3 Then C.phone
			When 4 Then C.fax
			When 5 Then C.payroll_rep
			When 6 Then CP.csr
			When 7 Then CP.cam
			When 8 Then CAST(COUNT(DISTINCT EC.employee_id) as varchar)
		End SortFld,
	@SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
WHERE C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND
	 (C.client_status='A' OR C.client_status='P')  AND C.phone LIKE @phone

GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam) TBL
Order by  case When SortOption=8  then cast(SortFld as int) else 0 end,
	case When SortOption<>8  then SortFld  else '' end
GO

-Ovatvvon :-Q

 

[jbenson001]

Try using a nested Case statement
First on sort order, then on sort direction.

 

[Ovatvvon]

That's what I was hoping to avoid. Is it not possible to just insert the parameter variable somewhere / somehow, so it would be a one or two line edit (much simpler)

-Ovatvvon :-Q

 

[jbenson001]

to do it your way, you would need to write dynamic sql... and you want to avoid that.

 

[Ovatvvon]

Why?

-Ovatvvon :-Q

 

[Ovatvvon]

Are you saying what you just suggested is considered dynamic sql? I thought you were saying d-sql is something else that I don't want to do, so it is better to do it the way you suggested. My appologies if I misunderstood.

-Ovatvvon :-Q

 

[jbenson001]

Dynamic SQL is not compiled as your SP is.

 

[jbenson001]

No problem..

 

[Ovatvvon]

Ok, I changed the code to the following, but now it doesn't sort at all, whereas before, it at least did an Ascending sort on the proper field. What did I do wrong?

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
	@company varchar(10),
	@id varchar(7),
	@name varchar(50),
	@status varchar(1),
	@phone varchar(16),
	@sortOrder int,
	@sortType varchar(4)
AS
IF @status != '0'
Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, 
            TBL.cam, employeeCount
FROM
( SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount, 
		Case @SortOrder When 1 Then Case @sortType When 'ASC' Then 'C.id ASC'
							When 'DESC' Then 'C.id DESC' end
			When 2 Then Case @sortType When 'ASC' Then 'C.dba_name ASC'
							When 'DESC' Then 'C.dba_name DESC' end
			When 3 Then Case @sortType When 'ASC' Then 'C.phone ASC'
							When 'DESC' Then 'C.phone DESC' end
			When 4 Then Case @sortType When 'ASC' Then 'C.fax ASC'
							When 'DESC' Then 'C.fax DESC' end
			When 5 Then Case @sortType When 'ASC' Then 'C.payroll_rep ASC'
							When 'DESC' Then 'C.payroll_rep DESC' end
			When 6 Then Case @sortType When 'ASC' Then 'CP.csr_rep ASC'
							When 'DESC' Then 'CP.csr DESC' end
			When 7 Then Case @sortType When 'ASC' Then 'CP.cam ASC'
							When 'DESC' Then 'CP.cam DESC' end
			When 8 Then Case @sortType When 'ASC' Then CAST(COUNT(DISTINCT EC.employee_id) as varchar) + ' ASC'
							When 'DESC' Then CAST(COUNT(DISTINCT EC.employee_id) as varchar) + ' DESC' end
		End SortFld,
        @SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
Where	C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND C.phone LIKE @phone AND C.client_status = @status
GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam ) TBL
Order by  case When SortOption=8  then cast(SortFld as int) else 0 end,
	case When SortOption<>8  then SortFld  else '' end

ELSE

Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, TBL.cam, employeeCount
FROM
(SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount,
		Case @SortOrder When 1 Then Case @sortType When 'ASC' Then 'C.id ASC'
							When 'DESC' Then 'C.id DESC' end
			When 2 Then Case @sortType When 'ASC' Then 'C.dba_name ASC'
							When 'DESC' Then 'C.dba_name DESC' end
			When 3 Then Case @sortType When 'ASC' Then 'C.phone ASC'
							When 'DESC' Then 'C.phone DESC' end
			When 4 Then Case @sortType When 'ASC' Then 'C.fax ASC'
							When 'DESC' Then 'C.fax DESC' end
			When 5 Then Case @sortType When 'ASC' Then 'C.payroll_rep ASC'
							When 'DESC' Then 'C.payroll_rep DESC' end
			When 6 Then Case @sortType When 'ASC' Then 'CP.csr_rep ASC'
							When 'DESC' Then 'CP.csr DESC' end
			When 7 Then Case @sortType When 'ASC' Then 'CP.cam ASC'
							When 'DESC' Then 'CP.cam DESC' end
			When 8 Then Case @sortType When 'ASC' Then CAST(COUNT(DISTINCT EC.employee_id) as varchar) + ' ASC'
							When 'DESC' Then CAST(COUNT(DISTINCT EC.employee_id) as varchar) + ' DESC' end
		End SortFld,
	@SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
WHERE C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND
	 (C.client_status='A' OR C.client_status='P')  AND C.phone LIKE @phone

GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam) TBL
Order by  case When SortOption=8  then cast(SortFld as int) else 0 end,
	case When SortOption<>8  then SortFld  else '' end
GO

-Ovatvvon :-Q

 

[Pattycake245]

Try this:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int,
    @sorttype varchar(4)
AS
IF @status != '0'
Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, 
            TBL.cam, employeeCount
FROM
( SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount, 
        Case @SortOrder When 1 Then C.id
            When 2 Then C.dba_name
            When 3 Then C.phone
            When 4 Then C.fax
            When 5 Then C.payroll_rep
            When 6 Then CP.csr
            When 7 Then CP.cam
            When 8 Then CAST(COUNT(DISTINCT EC.employee_id) as varchar)
        End SortFld,
        @SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
Where    C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND C.phone LIKE @phone AND C.client_status = @status
GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam ) TBL
Order by  [COLOR=red]case when @sorttype='desc' then null[/color]
When SortOption=8  then cast(SortFld as int) else 0 end [COLOR=red]desc[/color],
[COLOR=red]case when @sorttype='asc' then null[/color]
When SortOption<>8  then SortFld  else '' end [COLOR=red]asc[/color]

ELSE

Select TBL.id, TBL.dba_name, TBL.phone, TBL.fax, TBL.payroll_rep, TBL.csr, TBL.cam, employeeCount
FROM
(SELECT DISTINCT
                      Top 501 C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam, COUNT(DISTINCT EC.employee_id) AS employeeCount,
        Case @SortOrder When 1 Then C.id
            When 2 Then C.dba_name
            When 3 Then C.phone
            When 4 Then C.fax
            When 5 Then C.payroll_rep
            When 6 Then CP.csr
            When 7 Then CP.cam
            When 8 Then CAST(COUNT(DISTINCT EC.employee_id) as varchar)
        End SortFld,
    @SortOrder SortOption
FROM        Client C LEFT OUTER JOIN
                    CP_CLIENT  CP ON C.id = CP.CLIENT_id LEFT OUTER JOIN
                    EMPLOYEE_CLIENT  EC ON C.id = EC.CLIENT_id
WHERE C.id LIKE @id AND C.company LIKE @company AND C.dba_name LIKE @name AND
     (C.client_status='A' OR C.client_status='P')  AND C.phone LIKE @phone

GROUP BY C.id, C.dba_name, C.phone, C.fax, C.payroll_rep, CP.csr, CP.cam) TBL
Order by  case When SortOption=8  then cast(SortFld as int) else 0 end,
    case When SortOption<>8  then SortFld  else '' end
GO

See code in red in first select

Tim

 

[Ovatvvon]

It is switching the order now, but I can't figure out how. (It's not takeing the column number and (asc or desc) and adjusting the order by clause using those, I know that much. But can't quite figure out what it is doing / what is being sorted.

-Ovatvvon :-Q

 

[Ovatvvon]

This generates an error, so I know I didn't do it right, but shouldn't it be something along the lines of:

Order by  case when @sorttype='desc' then null
		When SortOption=8  then cast(SortFld as int) else 0 end desc,
		When SortOption<>8  then SortFld  else '' end desc
	  case when @sorttype='asc' then null
		When SortOption=8  then cast(SortFld as int) else 0 end asc,
		When SortOption<>8  then SortFld  else '' end asc

????

I don't know, I'm just trying to help figure this out...since it is my problem afterall.

-Ovatvvon :-Q

 

[Pattycake245]

You are getting error messages because you have more end statements than there are case statements. This is more what it should look like:

Order by  case when @sorttype='desc' then null
        When SortOption=8  then cast(SortFld as int) else 0 
        When SortOption<>8  then SortFld  else '' end desc,
      case when @sorttype='asc' then null
        When SortOption=8  then cast(SortFld as int) else 0
        When SortOption<>8  then SortFld  else '' end asc

I remember following this thread a little but are you basically trying to say that if 1 is passed as the @sortorder then you want to order by the C.id field and then you want to sort it either descending or ascending?

Tim

 

[Ovatvvon]

Correct, 1 = C.id field to sort by, and then @sortType will set it to either asc or desc.

-Ovatvvon :-Q

 

[Ovatvvon]

I pasted the correction, but I receive this error message:

Server: Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'When'.

which is this line:

Order by  case when @sorttype='desc' then null
        When SortOption=8  then cast(SortFld as int) else 0 
        [COLOR=red]When SortOption<>8  then SortFld  else '' end desc,[/color]

-Ovatvvon :-Q

 

[Pattycake245]

I guess you would never have a case where you would need the first else statement:

Order by  case when @sorttype='desc' then null
        When SortOption=8  then cast(SortFld as int)
        When SortOption<>8  then SortFld  else '' end desc,

Tim

 

[Ovatvvon]

I got it to work this way. Thank you for your help though everyone.

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int,
    @sorttype varchar(4)
AS
DECLARE @parameters nvarchar(150)
DECLARE @strSubSQL nvarChar(500)
DECLARE @strWhere nvarchar(220)
DECLARE @strGroupBy nvarchar(130)
DECLARE @strOrderBy nvarchar(100)
DECLARE @strSQL nvarchar(1300)

--------------------------------------------
-- Creating Parameters for EXEC Statement --
--------------------------------------------
SET @parameters = '@company varchar(10), @id varchar(7), @name varchar(50), @status varchar(1), @phone varchar(16), @sortOrder int, @sorttype varchar(4)'

------------------------------------------
-- Creating first part of SQL statement --
------------------------------------------
Set @strSubSQL = 'SELECT DISTINCT TOP 500 CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, CP_CLIENT.cam, COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id) AS employeeCount FROM CLIENT LEFT OUTER JOIN CP_CLIENT ON CLIENT.id = CP_CLIENT.client_id LEFT OUTER JOIN EMPLOYEE_CLIENT ON CLIENT.id = EMPLOYEE_CLIENT.client_id'

--------------------------------
-- Creating WHERE Clause --
--------------------------------
If @status != '0'
    BEGIN
	Set @strWhere = ' WHERE CLIENT.id LIKE ''' + @id + ''' AND CLIENT.company LIKE ''' + @company + ''' AND CLIENT.dba_name LIKE ''' + @name + ''' AND CLIENT.phone LIKE ''' + @phone + ''' AND CLIENT.client_status = ''' + @status + ''''
    END
Else
    BEGIN
	Set @strWhere = ' WHERE CLIENT.id LIKE ''' + @id + ''' AND CLIENT.company LIKE ''' + @company + ''' AND CLIENT.dba_name LIKE ''' + @name + ''' AND CLIENT.phone LIKE ''' + @phone + ''' AND (CLIENT.client_status=''A'' OR CLIENT.client_status=''P'')'
    END

------------------------------------------
-- Creating GROUP BY statement --
------------------------------------------
Set @strGroupBy = ' GROUP BY CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, CP_CLIENT.cam'

--------------------------------
-- Creating ORDER BY Clause --
--------------------------------
If @sortType = 'ASC'
    BEGIN
	Set @strOrderBy = Case @sortOrder
		When 1 Then ' ORDER BY CLIENT.id ASC'
		When 2 Then ' ORDER BY CLIENT.dba_name ASC'
		When 3 Then ' ORDER BY CLIENT.phone ASC'
		When 4 Then ' ORDER BY CLIENT.fax ASC'
		When 5 Then ' ORDER BY CLIENT.payroll_rep ASC'
		When 6 Then ' ORDER BY CP_CLIENT.csr ASC'
		When 7 Then ' ORDER BY CP_CLIENT.cam ASC'
		When 8 Then ' ORDER BY employeeCount ASC'
	End
    END
Else
    BEGIN
	Set @strOrderBy = Case @sortOrder
		When 1 Then ' ORDER BY CLIENT.id DESC'
		When 2 Then ' ORDER BY CLIENT.dba_name DESC'
		When 3 Then ' ORDER BY CLIENT.phone DESC'
		When 4 Then ' ORDER BY CLIENT.fax DESC'
		When 5 Then ' ORDER BY CLIENT.payroll_rep DESC'
		When 6 Then ' ORDER BY CP_CLIENT.csr DESC'
		When 7 Then ' ORDER BY CP_CLIENT.cam DESC'
		When 8 Then ' ORDER BY employeeCount DESC'
	End
    END

------------------------------------
-- Building entire SQL Statement --
------------------------------------
Set @strSQL = @strSubSQL + @strWhere + @strGroupBy + @strOrderBy


EXEC sp_executesql @strSQL, @parameters, @company, @id, @name, @status, @phone, @sortOrder, @sorttype
GO

-Ovatvvon :-Q