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)
 dynamic sort and remainder return

Author  Topic 

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-29 : 15:46:20
Ok, I have a table that I need to dynamically pick the sort field and
the sort column. I would like to show the sorted rs first then the
remaining records. Here is the paging script I am using to display the
rs. Everything works except for the sort column and the sort value, and I'm
not sure how to grab the remainder.

Any help would be appreciated.


ALTER PROCEDURE test
--s_cal_ordret_pg 1, 15, 4, 11
--sort def:
--type 4
-- 1 = show all
-- 2 = met file
-- 3 = prop add
-- 4 = company
-- 5 = customer

(
@page int,
@size int,
@oid int,
@lid int, --bring in for customer with single orders
@sort int=1,
@sv varchar(50)='%'
)
AS

SET NOCOUNT ON

--set start & end variables
DECLARE @start int
DECLARE @end int

SET @start = (((@page-1) * @size) + 1)
SET @end = (@start + @size -1)

--set sort

DECLARE @sortby varchar(15)

if @sort=1
SET @sortby='%'

if @sort=2
SET @sortby='a.metfileid'

if @sort=3
SET @sortby='a.propadd'

if @sort=4
SET @sortby='b.offname'

if @sort=5
SET @sortby='c.fname + '+ ' + c.lname'

DECLARE @pagedorders table (
OIDcount int IDENTITY(1,1) PRIMARY KEY,
ordid int,
metfileid int,
propadd varchar(50),
compname varchar(50),
customer varchar(50) )

insert @pagedorders
select a.ordid,
a.metfileid,
a.propadd,
b.offname,
c.fname + ' ' + c.lname
from tblorder a
inner join tblofficeprof b ON a.moid = b.oid
inner join tblusercust c ON a.ctuid = c.ctuid

?????????? here is my question can I do this

where a.moid=@oid AND @sortby like @sv + '%'

select(select count(*) from @pagedorders WHERE oidcount >= @end) as recleft,
(select count(*) from @pagedorders)as reccount,
left(oidcount,4),
ordid,
left(metfileid,52),
left(propadd,18),
left(compname,12),
left(customer,12)

from @pagedorders
where (oidcount >= @start) AND (oidcount <= @end)

slow down to move faster...

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-29 : 17:14:11
try
order by
case when @sort=2 then a.metfileid else null end ,
case when @sort=3 then a.propadd else null end ,
case when @sort=4 then a.offname else null end ,
case when @sort=5 then c.fname + '+ ' + c.lname else null end

if they are all the same datatype then you can do

order by
case @sort when 2 then a.metfileid
when 3 then a.propadd
when 4 then a.offname
when 5 then c.fname + '+ ' + c.lname
end

maybe a combination of the two.


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -