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

Ovatvvon · Jul 29, 2005

man this is frustrating. Now it goes back to the integer conversion error:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting the varchar value 'T02091' to a column of data type int.

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

I assumed that your ID column was an integer (only because that's what I do). Sorry.

change the Declare @Temp to be...

Code:

Declare @Temp
Table    (ID [red]VarChar(20)[/red],
        dba_name VarChar(50),
        Phone VarChar(16),
        Fax VarChar(16),
        payroll_rep VarChar(100),
        csr VarChar(100),
        cam VarChar(100),
        employeeCount Integer
        )

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Ovatvvon · Jul 29, 2005

I did, only I made it 7 instead of 20, because we won't ever have anything beyond that. I agree that an int would be better, unfortunately our client id's are created through some proprietary payroll software that we are integrating with, so we need to work with the apha-numeric values.

After changing everything, I still come up with the old:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting the varchar value 'T02091' to a column of data type int.

-Ovatvvon :-Q

sunila7 · Jul 29, 2005

Try this SP..

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 CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, 
            CP_CLIENT.cam, employeeCount
FROM
( 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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id)
               End SortFld
    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 ) TBL
Order by  case when isnumeric(SortFld) = 1 then  cast(SortFld as integer) else SortFld end
ELSE
Select CLIENT.id, CLIENT.dba_name, CLIENT.phone, CLIENT.fax, CLIENT.payroll_rep, CP_CLIENT.csr, 
            CP_CLIENT.cam, employeeCount
FROM
(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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id)
               End SortFld
    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) TBL
Order by  case when isnumeric(SortFld) = 1 then  cast(SortFld as integer) else SortFld end

GO

Sunil

Ovatvvon · Jul 29, 2005

I get a ton of errors with that...

Error 107: The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CP_CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CP_CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CP_CLIENT' does not match with a table name or alias name used in th equery.
The column prefix 'CP_CLIENT' does not match with a table name or alias name used in th equery.

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

This is starting to get ugly. Can you feel it?

I think it's time that you posted your code again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Ovatvvon · Jul 29, 2005

Oh ya, I can feel it. [thumbsdown]

Code:

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
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Declare @Temp
Table    (id varchar(7),
        dba_name VarChar(50),
        Phone VarChar(16),
        Fax VarChar(16),
        payroll_rep VarChar(10),
        csr VarChar(10),
        cam VarChar(10),
        employeeCount Integer
        )

IF @status != '0'
    Insert
    Into    @Temp
            (id,
            dba_name,
            Phone,
            Fax,
            payroll_rep,
            csr,
            cam,
            employeeCount)
    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
ELSE
    Insert
    Into    @Temp
            (id,
            dba_name,
            Phone,
            Fax,
            payroll_rep,
            csr,
            cam,
            employeeCount)
    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

Select     id,
        dba_name,
        Phone,
        Fax,
        payroll_rep,
        csr,
        cam,
        employeeCount
From    @Temp
Order By Case When @SortOrder = 1 Then ID
              When @SortOrder = 2 Then dba_name
              When @SortOrder = 3 Then phone
              When @SortOrder = 4 Then fax
              When @SortOrder = 5 Then payroll_rep
              When @SortOrder = 6 Then csr
              When @SortOrder = 7 Then cam
              When @SortOrder = 8 Then employeeCount
              End
GO

-Ovatvvon :-Q

sunila7 · Jul 29, 2005

Ok there were a few typos..
Try this:

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 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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id)
               End SortFld
    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 ) TBL
Order by  case when isnumeric(SortFld) = 1 then  cast(SortFld as integer) else SortFld 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 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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id)
               End SortFld
    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) TBL
Order by  case when isnumeric(SortFld) = 1 then  cast(SortFld as integer) else SortFld end

GO

Sunil

Ovatvvon · Aug 1, 2005

Thank you for the reply Sunila,

I copied and pasted your code in, and I still get the same error on the web page when processing:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting the varchar value 'T00003' to a column of data type int.

where T00003 is from the ID field. Since there are no such thing as datatypes in Classic ASP, why in the world does this error keep coming up. It has to be something with SQL Server...right? But the ID field is specified as VarChar, so I don't get it. This is definately during the return though. The SQL statement must have executed fine, it is just having problems with returning the data. [ponder]

Whoever can solve this I think deserves 5 stars!

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Ok Try this:

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 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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then CAST(COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id) as varchar)
               End SortFld,
		@SortOrder SortOption
    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 ) TBL
Order by  case When SortOption=8  then Isnumeric(SortFld) Else Sorfld 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 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,
              Case @SortOrder When 1 Then CLIENT.id
                  When 2 Then CLIENT.dba_name
                      When 3 Then CLIENT.phone
                      When 4 Then CLIENT.fax
                            When 5 Then CLIENT.payroll_rep
                      When 6 Then CP_CLIENT.csr
                      When 7 Then CP_CLIENT.cam
                      When 8 Then CAST(COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id) as Varchar)
               End SortFld,
		@SortOrder SortOption
    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) TBL
Order by  case When SortOption=8  then Isnumeric(SortFld) Else Sorfld end 

GO

Sunil

Ovatvvon · Aug 1, 2005

It is still giving the same error. [mad]

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Can you post some sample data and expected results?

Sunil

Ovatvvon · Aug 1, 2005

Input
_______________________________________________
Dropdown : "company" : [selects a company, or allows all companies with a Null value (determines which company the client is associated with - we are a PEO company)]
Textbox : "client id" : [Part or All of a client id, or empty (Null)]
Textbox : "client name" : [Part or all of a client's DBA name, or empty (Null)]
Textbox : "phone" : [Part or all of a client's contact phone number, or empty (Null)]
Radio Options : "active, pending or terminated" : [determins client account status, can be left blank (no option selected) to search for active and pending clients.

Expected Output - report style output
_______________________________________________
client id | client dba_name | phone | fax | client's payroll rep | client's csr | client's cam | # of employees at this client.

All fields will be sortable, asc or desc. Will be limited to 500 records returned based upon the web-user's input criteria.

Does this help?

-Ovatvvon :-Q

Ovatvvon · Aug 1, 2005

Whoops, forgot. There will also be the sortOrder input that will be handled via the ASP code and URL QueryStrings. If they click on a report/field header to sort by that, it'll refresh the page and pass the variable sortOrder as the field/column number to sort by. I also want to pass sortType, which will determin whether it will be sorted asc or desc. If no value is given, I am declaring the default sort order field to be the CLIENT.dba_name.

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

Yes this helps, but I would like to see some sample data in all the 3 tables and also their DataTypes would help...

Sunil

Ovatvvon · Aug 1, 2005

CLIENT
_________________________________
company: 40
id: T02001
status: A
dba_name: Big Company Tree Removal Services
phone: 1112223333
fax: 3332221111
payroll rep: asmith

CP_CLIENT
_________________________________
csr: bjohnson
cam: cjones

EMPLOYEE_CLIENT
_________________________________
employee_id: N33302

-Ovatvvon :-Q

Ovatvvon · Aug 1, 2005

Data Types...

CLENT
_______________________
company varchar(10)
id varchar(6)
client_status varchar(1)
dba_name varchar(50)
phone ut_Phone(varchar)(12)
fax ut_Phone(varchar)(12)
payroll_rep varchar(10)

CP_CLIENT
_______________________
csr varchar(10)
cam varchar(10)

EMPLOYEE_CLIENT
_______________________
employee_id varchar(7)

-Ovatvvon :-Q

Ovatvvon · Aug 1, 2005

Gee, you'd never think that trying to put in a dynamic order by clause into the stored procedure would cause so many problems, huh?

-Ovatvvon :-Q

sunila7 · Aug 1, 2005

I am not sure where you are having problem, I tried the query with the sample data you gave.. I am not getting any errors.. run this as below query in Query Analyzer and let us know if you are having any problems....

Also run the query after replacing the Table variables with actual table names and see if that works..

Code:

--- Sample Data
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,'T02001','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('T02001','bjohnson','cjones')


Declare @EMPLOYEE_CLIENT table 
(CLIENT_id varchar(20) ,employee_id varchar(10))
insert into @EMPLOYEE_CLIENT values('T02001','N33302')
---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 = 0
set @company = '40'
Set @id = 'T02001'
Set @name ='Big Company Tree%'
set @phone = '111223333%'

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 Isnumeric(SortFld) Else Sortfld 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 Isnumeric(SortFld) Else Sortfld end 

GO

Sunil

Ovatvvon · Aug 1, 2005

When I copy and paste that code in there directly, everything works just fine.

When I changed the tables to be real rather than variable tables, I run into the infamous error message again:

Server: Msg 245, Level 16, State 1, Line 45
Syntax error converting the varchar value 'T02076' to a column of data type int.

Perhaps I changed the code wrong? Here is how I changed it:

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 = 1
set @company = '%'
Set @id = 'T02076'
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 Isnumeric(SortFld) Else Sortfld 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 Isnumeric(SortFld) Else Sortfld end 

GO

-Ovatvvon :-Q

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

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor