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.
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 andthe 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 thers. 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)='%')ASSET NOCOUNT ON--set start & end variablesDECLARE @start int DECLARE @end intSET @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
|
tryorder 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 endif they are all the same datatype then you can doorder by case @sort when 2 then a.metfileidwhen 3 then a.propaddwhen 4 then a.offnamewhen 5 then c.fname + '+ ' + c.lnameendmaybe a combination of the two.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|