基於Ajax的模糊查詢輸入控制項（補充）

前幾天隨便發出這個控制項後，有很多朋友反映沒有資料來源，很不好意思。今天有點空把資料及一個相關的分頁預存程序貼出，需要的朋友可以在這裡下載：

--建樣本表

if exists (select * from dbo.sysobjects where id = object_id(N'[Subjects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Subjects]
GO

CREATE TABLE [Subjects] (
    [ID] [smallint] IDENTITY (1, 1) NOT NULL ,
    [SubjectName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [CnCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [SubjectCode] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [IsEnd] [bit] NULL ,
    CONSTRAINT [PK_Subjects] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO

--資料

INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 1 , '加值稅' , 'ZZS' , '01                  ' , 0 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 2 , '國內加值稅' , 'GNZZS' , '0101                ' , 0 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 3 , '國有企業加值稅' , 'GYQYZZS' , '010101              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 4 , '集體企業加值稅' , 'JTQYZZS' , '010102              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 5 , '股份制企業加值稅' , 'GFZQYZZS' , '010103              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 6 , '聯營企業加值稅' , 'LYQYZZS' , '010104              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 7 , '港澳台和外商投資企業加值稅' , 'GATHWSTZQYZZS' , '010105              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 8 , '私營企業加值稅' , 'SYQYZZS' , '010106              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 9 , '其他加值稅' , 'QTZZS' , '010119              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 10 , '加值稅稅金滯納金、罰款收入' , 'ZZSSKZNJ、FKSR' , '010120              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 11 , '福利企業加值稅退稅' , 'FLQYZZSTS' , '010121              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 12 , '軟體整合電路加值稅退稅' , 'RJJCDLZZSTS' , '010122              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 13 , '三線搬遷加值稅退稅' , 'SXBQZZSTS' , '010123              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 14 , '民貿企業加值稅退稅' , 'MMQYZZSTS' , '010124              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 15 , '宣傳文化單位加值稅退稅' , 'XCWHDWZZSTS' , '010125              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 16 , '森工綜合利用加值稅退稅' , 'SGZHLYZZSTS' , '010126              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 17 , '其他加值稅退稅' , 'QTZZSTS' , '010150              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 18 , '免抵調增加值稅' , 'MDDZZZS' , '010151              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 19 , '進口貨物加值稅' , 'JKHWZZS' , '0102                ' , 0 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 20 , '進口貨物加值稅' , 'JKHWZZS' , '010201              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 21 , '特定地區進口自用物資加值稅' , 'TDQYJKZYWZZZS' , '010202              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 22 , '進口貨物加值稅稅金滯納金、罰款收入' , 'JKHWZZSSKZNJ、FKSR' , '010220              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 23 , '進口貨物退加值稅' , 'JKHWTZZS' , '010221              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 24 , '特定地區進口自用物資退加值稅' , 'TDQYJKZYWZTZZS' , '010222              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 25 , '出口貨物退加值稅' , 'CKHWTZZS' , '0103                ' , 0 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 26 , '出口貨物退加值稅' , 'CKHWTZZS' , '010301              ' , 1 )
INSERT [Subjects] ( [ID] , [SubjectName] , [CnCode] , [SubjectCode] , [IsEnd] ) VALUES ( 27 , '免抵調減加值稅' , 'MDDJZZS' , '010302              ' , 1 )

--分頁預存程序

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[msp_Page]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure msp_Page
GO

/*****************************************************************
*功能描述：對查詢結果進行分頁
******************************************************************
*/
CREATE  PROCEDURE msp_Page
    (
        @Sql text = 'Select 1',
        @PageIndex int = 0,     --頁索引
        @PageSize int = 100, --頁記錄
        @rowcount int output, --總記錄
        @pagecount int output --總頁數
    )
AS
set nocount on

declare @P1 int

set @pagecount=(@PageIndex)*@pagesize+1    --頁號從0開始

exec sp_cursoropen @P1 output,@Sql,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1

IF( @rowcount % @PageSize <> 0 )
BEGIN
    SET @pagecount = (@rowcount/@PageSize) + 1
END
ELSE
BEGIN
    SET @pagecount = (@rowcount/@PageSize)
END

Select @rowcount,@pagecount

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO