USE [HJGINFODB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_FB_EX](
[ID] [bigint] NULL,
[CITY_ID] [int] NOT NULL,
[QU_ID] [int] NOT NULL,
[TYPE_ID] [int] NOT NULL,
[TYPE_PID] [int] NOT NULL,
[STITLE] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[JG] [int] NOT NULL,
[INSTIME] [datetime] NULL CONSTRAINT [DF_TB_FB_EX_INSTIME] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [HJGINFODB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[f_div_TB_FB_EX]
@CITY_ID int,
@QU_ID int,
@TYPE_PID int,
@TYPE_ID int,
@PageIndex Int,
@PageSize Int,
@ReturnRecordCount int out
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex+1
SET @PageUpperBound = @PageLowerBound + @PageSize-1
if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME ,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_PID=@TYPE_PID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID<>0 and @QU_ID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_PID<>0 and @QU_ID<>0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and QU_ID=@QU_ID and TYPE_PID=@TYPE_PID ) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID<>0 and @QU_ID<>0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID and TYPE_ID=@TYPE_ID and QU_ID=@QU_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end
else if @CITY_ID<>0 and @TYPE_ID=0 and @QU_ID=0 and @TYPE_PID=0
begin
select @ReturnRecordCount = count(*) from TB_FB_EX with (nolock)
where CITY_ID = @CITY_ID
SELECT *
FROM (select ID,CITY_ID,QU_ID,
TYPE_ID,TYPE_PID,STITLE,JG,INSTIME,ROW_NUMBER() OVER (order by ID desc) AS SQL_RowNumber
FROM TB_FB_EX with (nolock) where CITY_ID = @CITY_ID) AS NormRen
where SQL_RowNumber BETWEEN @PageLowerBound AND @PageUpperBound
end