Add sort desc or sort asc to storedprocedure

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)

Add sort desc or sort asc to storedprocedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-02-24 : 07:02:19

I am passing variables to my sp to control the 'order by'. I also want to pass whether the the data is sorted ASC or DESC. Here's my sp, but it's not working. If I remove @strSortOrder it works. Thanks for any help.

CREATE PROCEDURE spRMU_GetFilesforRequestFiltered

@strDepartment nvarchar(100),
@strDivision nvarchar(100),
@strSection nvarchar(100),
@strFileRef nvarchar(100),
@strSubject nvarchar(1000),
@strDescription nvarchar(1000),
@strBoxNo nvarchar(10),
@strConNo nvarchar(10),
@strViewRestricted bit,
@strSortString nvarchar(200),
@strSortOrder nvarchar(8)

AS

IF @strViewRestricted = 1

BEGIN
SELECT *
FROM tblFiles
WHERE
Dept = @strDepartment and
Division =@strDivision and
Sect like '%'+@strSection+'%' and
FileRef like '%' + @strFileRef + '%' and
Subject like '%'+@strSubject+'%' and
FileDescription like '%'+@strDescription+'%' and
BoxNo like '%'+cast(@strBoxNo as varchar(50))+'%' and
ConNo like '%'+cast(@strConNo as varchar(50))+'%'
ORDER BY
CASE WHEN @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescription' then FileDescription
+ @strSortOrder
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-24 : 07:34:19

This should do it:

...
ORDER BY CASE WHEN @strSortOrder = 'DESC' THEN null ELSE
CASE WHEN @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescription' then FileDescription END
END ASC,
CASE WHEN @strSortOrder = 'ASC' THEN null ELSE
CASE WHEN @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescription' then FileDescription END
END DESC

Don't know why you need nvarchar for those two parameters, or why you need 8 characters to store "ASC" or "DESC".

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-02-24 : 07:38:58

Thanks for your help - to answer your questions, cos I'm a newbie to all this !

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-02-24 : 10:33:59

It's not sorting anything.....

CREATE PROCEDURE spRMU_GetFilesforRequestFiltered

@strDepartment nvarchar(100),
@strDivision nvarchar(100),
@strSection nvarchar(100),
@strFileRef nvarchar(100),
@strSubject nvarchar(1000),
@strDescription nvarchar(1000),
@strBoxNo nvarchar(10),
@strConNo nvarchar(10),
@strViewRestricted bit,
@strSortString nvarchar(200),
@strSortOrder nvarchar(4)

AS

IF @strViewRestricted = 1

BEGIN
SELECT *
FROM tblFiles
WHERE
Dept = @strDepartment and
Division =@strDivision and
Sect like '%'+@strSection+'%' and
FileRef like '%' + @strFileRef + '%' and
Subject like '%'+@strSubject+'%' and
FileDescription like '%'+@strDescription+'%' and
BoxNo like '%'+cast(@strBoxNo as varchar(50))+'%' and
ConNo like '%'+cast(@strConNo as varchar(50))+'%'

ORDER BY CASE WHEN @strSortOrder = 'DESC' THEN null ELSE
CASE WHEN @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescription' then FileDescription END
END ASC,
CASE WHEN @strSortOrder = 'ASC' THEN null ELSE
CASE WHEN @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescription' then FileDescription END
END DESC

END
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-24 : 11:01:38

are you passing in the correct parameters? how are you calling it? Should SubjectAZ for descending be SubjectZA ?

- Jeff

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-02-24 : 11:14:16

mmmm I think its lack of communication between fingers and brain - sorry. Typos again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources