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

Ideally it would be best if I could just specify the column/field number rather than the field name.

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

ORDER BY @sortOrder

To....

Code:

Order By Case 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

-George

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

gmmastros · Jul 29, 2005

Sorry. Here's what I meant

Code:

Order By Case When @SortOrder = 1 Then CLIENT.id
              When @SortOrder = 2 Then CLIENT.dba_name
              When @SortOrder = 3 Then CLIENT.phone
              When @SortOrder = 4 Then CLIENT.fax
              When @SortOrder = 5 Then CLIENT.payroll_rep
              When @SortOrder = 6 Then CP_CLIENT.csr
              When @SortOrder = 7 Then CP_CLIENT.cam
              When @SortOrder = 8 Then COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id)
              End

-George

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

Ovatvvon · Jul 29, 2005

Thank you for replying George.

I am now getting this error:

Error 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I tried switching the ORDER BY statement you provided from COUNT(DISTINCT EMPLOYEE_CLIENT.employee_id) to just employeeCount, but that did not fix the problem. Any ideas?

-Ovatvvon :-Q

Ovatvvon · Jul 29, 2005

All the items are in the select statement, so this is kind of confusing.

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

What happens when you comment out When @SortOrder = 8...

Try running the query sorting on several different columns (not the 8th). Does it work then?

-George

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

Ovatvvon · Jul 29, 2005

After taking out the 8th, it still generates the error, only this time it displays the error once, instead of repeating itself. (I did take it out of both locations as well.)

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

Unfortunately, I don't know a simple way to resolve this problem.

The only thing that comes to mind is to insert the data in to a temp table or table variable without sorting and then select from the temp table or table variable using the order by.

If you're dealing with a lot of records, performance may degrade.

-George

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

Ovatvvon · Jul 29, 2005

Sorry, I need to withdraw that statement. Forget the part where it is listed only one time. It is twice. I just had removed the DISTINCT from one the select statements to try something out, and forgot about it. It does still generate the error : written twice as well.

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

This is completely untested. I also guessed the data types for the payroll_rep, csr, and cam fields.

Code:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int
AS
Declare @Temp
Table	(ID Integer,
		dba_name VarChar(50),
		Phone VarChar(16),
		Fax VarChar(16),
		payroll_rep VarChar(100),
		csr VarChar(100),
		cam VarChar(100),
		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

-George

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

Ovatvvon · Jul 29, 2005

lol, well, that part checks out now with SQL Server, however, an error is generated on the asp page now:

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

T00003 is from the CLIENT.id field, so it is specified as a varchar. How could it be trying to convert it to an integer?

The actual line where it fails is on the check for End of File for the rstInfo recordset in the following function which takes the recordset and pulls it into an array:

Code:

Function returnSQLArray(byref cnnDB, byref strSP, byref arrResults)
'On Error Resume Next
Dim rstInfo
	returnSQLArray = False
	Set rstInfo = cnnDB.Execute(strSP)
		If Not rstInfo.EOF Then
			arrResults = rstInfo.GetRows
			returnSQLArray = True
		Else
			reDim arrResults(0,0)
		End If
		
		If Err.Number > 0 Then
			returnSQLArray = False
			reDim arrResults(0,0)
		End If
		
		rstInfo.Close
	Set rstInfo = Nothing
End Function

So, it still seems to be a problem with the stored procedure (or, at least some interaction with it.) I just don't see how it is trying to convert it to an integer when it is specified as a varchar.

-Ovatvvon :-Q

Pattycake245 · Jul 29, 2005

I would guess that based on the error message you are getting that it is failing when trying to insert into your @temp table. You declare the table with ID as int and if you are trying to insert an ID like T000003, it isn't going to like it. Try changing the ID field to varchar.

Tim

Ovatvvon · Jul 29, 2005

Ok, yep, I didn't see that it was changed to integer. So I changed that, which got rid of that problem, but now, there is yet again, another error. And this one I'm really baffeled at. It is having an error on the line checking for the recordset end of file, as stated above.

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

Is there anything with this SP that would generate an error like that?

-Ovatvvon :-Q

Ovatvvon · Jul 29, 2005

Also, just so I can understand better for future knowledge, why is the temp table necessary for this to work?

-Ovatvvon :-Q

gmmastros · Jul 29, 2005

The temp table allows us to dynamically set the sort order.

I've seen the [red]Operation is not allowed when the object is closed.[/red] message before. Add a line to the sproc.

Code:

CREATE     PROCEDURE [dbo].[sql_searchClientAccount]
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16),
    @sortOrder int
AS
[red]SET NOCOUNT ON[/red]
Declare @Temp

-George

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

sunila7 · Jul 29, 2005

The reason why you are gettting the error about
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
" is that Order By Field is not one of the fields in the return SQL statement
So, if you change the query suggested by George like this it should work

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,
		      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
    ORDER BY  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 
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,
		      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
    ORDER BY 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 
GO

Hope this Helps.

Sunil

Ovatvvon · Jul 29, 2005

I added that line; now it turns into the following error:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
Warning: Null value is eliminated by an aggregate or other SET operation.

So, everyone of these should be able to take in a Null value, which is why I use the Like clause, and in the code insert a wild card for them (%). The exception is the sortOrder, which will always be present.

What would be causing the problem with a Null value? (Bet this is turning into much more than anticipated, huh? Me too. I really thank you for all the time you're putting into helping!!)

-Ovatvvon :-Q

Ovatvvon · Jul 29, 2005

sunila7,

I tried implementing your code, but it went back to the integer problem again:

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

-Ovatvvon :-Q

sunila7 · Jul 29, 2005

After SET NOCOUNT ON as suggested by George add

SET ANSI_WARNINGS OFF

This should fix the "Null value eliminated" problem.

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

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor