34,838
社区成员




CREATE PROCEDURE dbo.getBuyList
@iPageCount int OUTPUT, --总页数
@iPage int, --当前页号
@iPageSize int, --每页记录数
@strTemp nvarchar(200) --搜索条件
as
set nocount on
begin
if @strTemp is null return
--创建临时表
create table #TempBuy (ID int IDENTITY, --自增字段
Buy_ID int,
Buy_InfoType tinyint,
Buy_Type nvarchar(16),
Buy_Title nvarchar(128),
Buy_Money nvarchar(16),
Buy_RegDate datetime,
Buy_Content text,
Buy_Picture nvarchar(32),
Buy_SmallClassID smallint,
Buy_LinkMan nvarchar(16),
Buy_Tel nvarchar(32),
Buy_shi nvarchar(16),
Buy_qu nvarchar(16),
Buy_SmallClassName nvarchar(16))
--设置临时表的主键
ALTER TABLE #TempBuy WITH NOCHECK ADD
CONSTRAINT [PK_#TempBuy] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
Declare @strSQL nvarchar(1000)
Set @strSQL = N'insert into #TempBuy(Buy_ID,Buy_InfoType,Buy_Type,Buy_Title,Buy_Money,Buy_RegDate,Buy_Content,Buy_Picture,Buy_SmallClassID,Buy_LinkMan,Buy_Tel,Buy_shi,Buy_qu,Buy_SmallClassName) SELECT Buy.ID,Buy.InfoType,Buy.Type,Buy.Title,Buy.Money,Buy.RegDate,Buy.Content,Buy.Picture,Buy.SmallClassID,Buy.LinkMan,Buy.Tel,shi.shi,qu.qu,SmallClass1.SmallClassName FROM (((Buy INNER JOIN shi ON Buy.shi=shi.ID)) INNER JOIN qu ON Buy.qu=qu.ID) INNER JOIN SmallClass1 ON Buy.SmallClassID=SmallClass1.ID WHERE ' + @strTemp + ' order by case infoType when 0 then 0 else 1 end desc,RegDate desc,Buy.ID Desc;'
--向临时表中写入数据
execute sp_executesql @strSQL
--取得记录总数
declare @iRecordCount int
set @iRecordCount = @@rowcount
--确定总页数
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
--若请求的页号大于总页数,则显示最后一页
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
--确定当前页的始末记录
DECLARE @iStart int --start record
DECLARE @iEnd int --end record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
--取当前页记录
select * from #TempBuy where [ID]>@iStart and [ID]<@iEnd
--删除临时表
DROP TABLE #TempBuy
--返回记录总数
return @iRecordCount
end
GO
SELECT Buy.ID,Buy.Title,Buy.SmallClassID,shi.shiName,qu.quName,SmallClass.SmallClassName
FROM Buy join shi on Buy.shi=shi.ID join qu on Buy.qu=qu.ID
join SmallClass on Buy.SmallClassID=SmallClass.ID
where buy.SmallClassID=68 and buy.shi=1 and buy.IsCheck=1