基于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

posted @ 2007-12-04 17:37 冰戈 阅读(...) 评论(...) 编辑 收藏