|
skillile
Posting Yak Master
USA
208 Posts |
 Posted - 11/29/2001 : 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
United Kingdom
12543 Posts |
Posted - 11/29/2001 : 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. |
 |
|
dynamic sort and remainder return
