Putting variables in my ORDER BY clause.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Putting variables in my ORDER BY clause.

Author

Topic

Pete Mounce
Starting Member

4 Posts

Posted - 2004-03-09 : 06:02:43

Based on http://dbforums.com/arch/29/2003/3/733876 I have written the following stored procedure. The point is to carry out sorting on the database server, as opposed to inside the application - thus, I need to pass the query the column-name to sort by, and the direction.

The following is just with sorting, not bi-directional:


CREATE PROCEDURE GetCompanies
	@Criteria NVARCHAR(50),
	@inName BIT,
	@inDescription BIT,
	@inRelatedProjects BIT,
	@SortExpr VARCHAR(30),
	@SortASC BIT
AS
DECLARE @SearchStr nvarchar(52)
IF @Criteria != '' BEGIN
	SET @SearchStr = '%' + @Criteria + '%'
END ELSE BEGIN
	SET @SearchStr = '%'
END

SELECT c.CompanyID, c.Name, c.Description, c.RelatedProjects, c.Stamp
FROM tCompany c
WHERE @Criteria = ''
	OR (
		(@inName = 1            AND c.Name            LIKE @SearchStr) OR
		(@inDescription = 1     AND c.Description     LIKE @SearchStr) OR
		(@inRelatedProjects = 1 AND c.RelatedProjects LIKE @SearchStr)
	)
ORDER BY
	CASE @sortExpr WHEN 'name' THEN c.Name ELSE NULL END,
	CASE @sortExpr WHEN 'description' THEN c.Description ELSE NULL END,
	CASE @sortExpr WHEN 'relatedprojects' THEN c.RelatedProjects ELSE NULL END

GO

What I want to do is further specify the sort such that I can define the direction. I tried:

snippet from ORDER BY clause:
	CASE WHEN (@sortExpr = 'name' AND @SortAsc = 1) THEN c.Name ASC ELSE NULL END,
	CASE WHEN (@sortExpr = 'name' AND @SortAsc = 0) THEN c.Name DESC ELSE NULL END

but I get invalid syntax near ASC. Why? What is the correct syntax to use in this case?

Am I going to have to abandon bi-directional sorting?

Pete Mounce
Starting Member

4 Posts

Posted - 2004-03-09 : 06:08:44

Oh - table structure for tCompany is

CompanyID INT (identity)
Stamp DATETIME
Name NVARCHAR(50)
Description NVARCHAR(250)
RelatedProjects NVARCHAR(500)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-09 : 06:29:09

CASE WHEN (@sortExpr = 'name' AND @SortAsc = 1) THEN c.Name ELSE NULL END ASC ,
CASE WHEN (@sortExpr = 'name' AND @SortAsc = 0) THEN c.Name ELSE NULL END DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pete Mounce
Starting Member

4 Posts

Posted - 2004-03-09 : 06:36:42

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources