28,408
社区成员




CREATE PROCEDURE [dbo].[Web_Page]
@tablename varchar(100),
@tablefield varchar(20),
@where varchar(5000),
@orderby varchar(500),
@fieldlist varchar(1000),
@curpage int,
@page_record int,
@sort varchar(8),
@groupby varchar(5000),
@count varchar(100) output
AS
BEGIN
DECLARE @cmd varchar(8000)
DECLARE @uprecord int
DECLARE @Op varchar(2) -- 操作符
DECLARE @max_min varchar(4) -- 最大/最小计算
SET @uprecord=@curpage * @page_record
declare @tmpsql nvarchar(1000)
exec('Select '+@tablefield+' FROM '+@tablename+' Where '+@where+' '+@groupby)
--exec sp_executesql @tmpsql,N'@tmpcount int output',@count output
set @count=@@rowcount
SET @cmd='Select TOP '+cast(@page_record AS NVARCHAR)+' '+@fieldlist+' FROM '+@tablename+' Where '+@where+' AND '+@tablefield+' NOT IN ('+
'Select TOP '+cast(@uprecord AS NVARCHAR)+' '+@tablefield+' FROM '+@tablename+' Where '+@where+' '+@groupby+' '+@orderby+') '+@groupby+' '+@orderby
EXEC(@cmd)
RETURN @count
END
set cmd=Server.CreateObject ("ADODB.Command")
cmd.activeconnection=strCon
cmd.CommandText="WEB_PAGE"
cmd.CommandType=4
cmd.prepared=true
cmd.Parameters.Append cmd.CreateParameter("@tablename",200,1,100,tableName)
cmd.Parameters.Append cmd.CreateParameter("@tablefield",200,1,20,tableField)
cmd.Parameters.Append cmd.CreateParameter("@where",200,1,1000,tmpWhere)
cmd.Parameters.Append cmd.CreateParameter("@orderby",200,1,100,orderStr)
cmd.Parameters.Append cmd.CreateParameter("@fieldlist",200,1,1000,fieldList)
cmd.Parameters.Append cmd.CreateParameter("@curpage",200,1,100,curPage)
cmd.Parameters.Append cmd.CreateParameter("@page_record",200,1,100,pageRecord)
cmd.Parameters.Append cmd.CreateParameter("@sort",200,1,100,"")
cmd.Parameters.Append cmd.CreateParameter("@groupby",200,1,100,"")
cmd.Parameters.Append cmd.CreateParameter("@count",200,2,100)
set rs=cmd.execute
totalCount=cmd.Parameters("@count").value
response.Write("totalCount=" & totalCount)
set rs=nothing
set cmd.activeconnection=nothing
set cmd=nothing
USE [SDMS]
GO
DECLARE @return_value int,
@count int
EXEC @return_value = [dbo].[Web_Page]
@tablename = N'CMSETH,CTN',
@tablefield = N'CTN.REF',
@where = 'CMSETH.ref = CTN.ref AND CMSETH.APPR IS NULL AND CONVERT(VARCHAR(10),TMIN1,120)>=''2014-01-20'' AND CONVERT(VARCHAR(10),TMIN1,120)<=''2014-01-20''',
@orderby = ' ORDER BY CMSETH.EDTE',
@fieldlist = N'CTN.ref, CTN.oper,CTN.SYS, CMSETH.refe, CTN.depo, CMSETH.edte, CMSETH.appr, CMSETH.cdte, CMSETH.udte,CMSETH.hrate, CMSETH.rhour, CMAT+DMAT+OMAT+WMAT AS MAT',
@curpage = '0',
@page_record = '30',
@sort = '',
@groupby = N'',
@count = @count OUTPUT
SELECT 'Return Value' = @return_value
SELECT @count as N'@count'
GO