SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 dynamic sort and remainder return
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skillile
Posting Yak Master

USA
208 Posts

Posted - 11/29/2001 :  15:46:20  Show Profile  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000