以下是分页排序存储过程,但每次对类型为字符串列进行排序查询出的记录顺序都会不一样。哪位能帮忙解决一下每次排序的内容都一样
CREATE proc GetHouseList(@pageIndex int,@pageSize int,@sortExpression nvarchar(100),@strCondition nvarchar(1000))
as if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','10.6.1.27'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'EvaluateUser','EvaluateUser'
declare @ccaiSql nvarchar(3000)
declare @endIndex varchar(8)
declare @startIndex varchar(8)
declare @strColumnExpression nvarchar(1000)
declare @strSql nvarchar(200)
declare @PageLowerBound int
select @endIndex=cast((@pageIndex+1)*@pageSize as varchar(8));
select @startIndex=cast(@pageIndex*@pageSize as varchar(8));
set @PageLowerBound=(@pageindex+1)*@pagesize
select @strSql=''select @strColumnExpression=@strCondition+' order by '+@sortExpression;--print @strColumnExpression
create table #temphouse(
autoID int identity(1,1) not null,
ContractDate datetime,
PropertyName varchar(100),
Address varchar(100),
RoomNo varchar(500),
PropertyType varchar(20),
[Floor] varchar(20),
AreaName varchar(50),
DeptName varchar(50),
Price money,
[Square] varchar(20),
AveragePrice money)
set @ccaiSql='set rowcount @PageLowerBound
insert into #temphouse(ContractDate,PropertyName,Address,RoomNo,PropertyType,[Floor],AreaName,DeptName,Price,[Square],AveragePrice)
select * from (select ContractDate,PropertyName,Address,RoomNo,
PropertyType,[Floor],isnull(AreaName,'''') as AreaName,isnull(DeptName,'''') as DeptName,Price,
[Square],case when [square]=0 then 0 else price/[square] end as AveragePrice from srv_lnk.SZCCAI.dbo.VW_ContractResearchCenterExport ) as CCAIHOUSE '+@strColumnExpressionexecute sp_executesql @ccaiSql ,N'@PageLowerBound int' ,@PageLowerBound
select @strSql=@strSql+'select * from (select top '
select @strSql=@strSql+@endIndex+'* from #temphouse order by autoid'
select @strSql=@strSql+') houseList where autoID>'+@startIndexexecute sp_executesql @strSql
drop table #temphouseGO
CREATE proc GetHouseList(@pageIndex int,@pageSize int,@sortExpression nvarchar(100),@strCondition nvarchar(1000))
as if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','10.6.1.27'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'EvaluateUser','EvaluateUser'
declare @ccaiSql nvarchar(3000)
declare @endIndex varchar(8)
declare @startIndex varchar(8)
declare @strColumnExpression nvarchar(1000)
declare @strSql nvarchar(200)
declare @PageLowerBound int
select @endIndex=cast((@pageIndex+1)*@pageSize as varchar(8));
select @startIndex=cast(@pageIndex*@pageSize as varchar(8));
set @PageLowerBound=(@pageindex+1)*@pagesize
select @strSql=''select @strColumnExpression=@strCondition+' order by '+@sortExpression;--print @strColumnExpression
create table #temphouse(
autoID int identity(1,1) not null,
ContractDate datetime,
PropertyName varchar(100),
Address varchar(100),
RoomNo varchar(500),
PropertyType varchar(20),
[Floor] varchar(20),
AreaName varchar(50),
DeptName varchar(50),
Price money,
[Square] varchar(20),
AveragePrice money)
set @ccaiSql='set rowcount @PageLowerBound
insert into #temphouse(ContractDate,PropertyName,Address,RoomNo,PropertyType,[Floor],AreaName,DeptName,Price,[Square],AveragePrice)
select * from (select ContractDate,PropertyName,Address,RoomNo,
PropertyType,[Floor],isnull(AreaName,'''') as AreaName,isnull(DeptName,'''') as DeptName,Price,
[Square],case when [square]=0 then 0 else price/[square] end as AveragePrice from srv_lnk.SZCCAI.dbo.VW_ContractResearchCenterExport ) as CCAIHOUSE '+@strColumnExpressionexecute sp_executesql @ccaiSql ,N'@PageLowerBound int' ,@PageLowerBound
select @strSql=@strSql+'select * from (select top '
select @strSql=@strSql+@endIndex+'* from #temphouse order by autoid'
select @strSql=@strSql+') houseList where autoID>'+@startIndexexecute sp_executesql @strSql
drop table #temphouseGO
呵呵 print @strSql 看看错误在哪里
在 看看,有什么不对劲的!
insert into #temphouse(ContractDate,PropertyName,Address,RoomNo,PropertyType,[Floor],AreaName,DeptName,Price,[Square],AveragePrice)
select * from (select ContractDate,PropertyName,Address,RoomNo,
PropertyType,[Floor],isnull(AreaName,'') as AreaName,isnull(DeptName,'') as DeptName,Price,
[Square],case when [square]=0 then 0 else price/[square] end as AveragePrice from srv_lnk.SZCCAI.dbo.VW_ContractResearchCenterExport ) as CCAIHOUSE order by propertyname