Is this sp ok ??? - SQL Server Forums

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

Transact-SQL (2000)

Is this sp ok ???

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-16 : 06:59:49

I have the following sp. Some of the sort fields will be empty. Will these always be put in the same order if I sort ASC - because they aren't ! I wondered if it's a bug in my sp logic.

CREATE PROCEDURE spRMU_GetFilesforRequestFiltered1

@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 @strSortString='SubjectAZ' then Subject
WHEN @strSortString='FileDescriptionAZ' then FileDescription
WHEN @strSortString='FileRefAZ' then FileRef
else Subject
END ASC

END
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 07:27:04

well they will be orderd ascending wise... if you have 10 rows with null, and 5 with value "1" then the order willl be
first the 10 null rows then the 5 "1" rows. the orders of the 10 or 5 rows isn't guarnateed to be always the same.
you'd need to sort on another column for that.

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-16 : 07:34:56

Thanks Spirit1. I thought that they may be in the order they appeared in the sql table if they were all nulls/empty

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 08:57:05

Worthwhile always including the primary key fields in a sort that has duplciates - that way the ordering is "repeatable" - otherwise although they may appear in the same order each time other activities (such as rebuilding indexes etc.) may cause them to display in a different order

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-16 : 09:01:57

Do you mean

ORDER BY FIELD1, FIELD2

where FIELD2 is the key of the table ?

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:36:52

Exactly - if you've got several Primary Key fields you would do

ORDER BY DataColumn1, DataColumn2, PKField1, PKField2 ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources