possible to have dynamic Order By in stored procedure? 1

Ovatvvon · Jul 29, 2005

Hello all,

Situation:
I am setting up a stored procedure for a report to be printed on the webpage. I want to allow any field to be sorted ascending or descending when they click on the table column header name. To speed everything up, I copy the recordset into a multi-dimentional array, and then close the recordset and db connections.

The problem:
Because I am using the array, I cannot retrieve the field name into the array. When I create the table column header names to be links, I want to specify a querystring to be attached to it to specify that it is that column that should be the order by field. Since I cannot pull the name of the field from the recordset (because I am using arrays), I created a static function with the field names. So now the problem is that SQL Server will not let me create a variable to intake the value for the order by statement. Here is what I have for code...

Code:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
	@company varchar(10),
	@id varchar(7),
	@name varchar(50),
	@status varchar(1),
	@phone varchar(16),
	@sortOrder int
AS
IF @status != '0'
	SELECT DISTINCT
                      Top 501 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
	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
	GROUP BY CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, CP_CLIENT.cam
	ORDER BY @sortOrder
ELSE
	SELECT DISTINCT
                      Top 501 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
	WHERE CLIENT.id LIKE @id AND CLIENT.company LIKE @company AND CLIENT.dba_name LIKE @name AND (CLIENT.client_status='A' OR CLIENT.client_status='P') AND CLIENT.phone LIKE @phone
	GROUP BY CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, CP_CLIENT.cam
	ORDER BY @sortOrder
GO

But when I try to create this so that sortOrder will be the parameter for the order by statement, SQL Server generates the following error:

Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Does anyone know why this is happening, and how I can make it do what I'm trying to do?

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Try this... Replace the Table variables with Actual table names..

Code:

Declare @Client Table (company varchar(50),
id varchar(20),
status varchar(1),
dba_name varchar(200),
phone varchar(100),
fax varchar(100),
payroll_rep varchar(20))
insert into  @CLIENT values(40,'T02076','A','Big Company Tree Removal Services',111223333,3332221111,'asmith')
insert into  @CLIENT values(40,'T02077','A','Big Company Tree Removal Services',111223333,3332221111,'asmith')

Declare @CP_CLIENT table (
CLIENT_id varchar(20),
csr varchar(25),
cam varchar(55))
insert into @CP_CLIENT values('T02076','bjohnson','cjones')
insert into @CP_CLIENT values('T02076','bjohnson','cjones')

insert into @CP_CLIENT values('T02077','bjohnson','cjones')
insert into @CP_CLIENT values('T02077','bjohnson','cjones')


Declare @EMPLOYEE_CLIENT table 
(CLIENT_id varchar(20) ,employee_id varchar(10))
insert into @EMPLOYEE_CLIENT values('T02076','N33301')
insert into @EMPLOYEE_CLIENT values('T02076','N33302')
insert into @EMPLOYEE_CLIENT values('T02076','N33311')
insert into @EMPLOYEE_CLIENT values('T02076','N33312')

insert into @EMPLOYEE_CLIENT values('T02077','N33303')
insert into @EMPLOYEE_CLIENT values('T02077','N33304')
insert into @EMPLOYEE_CLIENT values('T02077','N33305')

---Sample Data
declare   @status varchar(1) 
declare    @sortOrder int 

declare    @company varchar(10)
declare    @id varchar(7)
declare    @name varchar(50)
declare    @phone varchar(16)


set @Status =  '0'
set @sortorder = 8
set @company = '%'
Set @id = 'T0207%'
Set @name ='%'
set @phone = '%'

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 SortOption,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

Sunil

Ovatvvon · Aug 1, 2005

PHEW. I think we may Have It!! (Just to clarify, (because this is just a touch above my head)...to change the table variables to actual names, you wanted me to delete the sections such as:

Code:

Declare @Client Table (company varchar(50),
id varchar(20),
status varchar(1),
dba_name varchar(200),
phone varchar(100),
fax varchar(100),
payroll_rep varchar(20))
insert into  @CLIENT values(40,'T02076','A','Big Company Tree Removal Services',111223333,3332221111,'asmith')
insert into  @CLIENT values(40,'T02077','A','Big Company Tree Removal Services',111223333,3332221111,'asmith')

and change all the "variable" table names referenced throughout the code, correct?

So I did that and this is what it looks like below. When run in Q.A., everything seems to work good.

Code:

---Sample Data
declare   @status varchar(1) 
declare    @sortOrder int 

declare    @company varchar(10)
declare    @id varchar(7)
declare    @name varchar(50)
declare    @phone varchar(16)


set @Status =  '0'
set @sortorder = 8
set @company = '%'
Set @id = 'T0207%'
Set @name ='%'
set @phone = '%'

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 SortOption,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

please let me know if I did that right.

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Yes.. change your Stored procedure like this and call it from ASP front end and see if it is working fine.

Code:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int
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 SortOption,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

Sunil

Ovatvvon · Aug 1, 2005

You're a GENIUS Sunil! I only wish I could give you more than one star!!!

Seems to be working perfectly now. I'm going to have to go over these postings more in detail and really analyze everything so I have a better understanding of everything.

Thank you very much!

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Glad to know that it is working... The reason why you were getting the error message was because of the order by Clause. SQL Server was not able to convert some of the values to integer. Try running one of the earlier code after commenting out the order by clause.. it should work. Sample data was helpful in detecting where the problem was occuring.

Sunil

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

possible to have dynamic Order By in stored procedure? 1

Ovatvvon

Programmer

sunila7

Technical User

Ovatvvon

Programmer

sunila7

Technical User

Ovatvvon

Programmer

sunila7

Technical User

Similar threads

Part and Inventory Search

Sponsor