set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_TopWinCMS_PageList]
(
@TableName nvarchar(1000), --表名,可以是多个表,但不能用别名
@FieldKey nvarchar(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields nvarchar(1000) = '*', --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@WHERE nvarchar(1000) = NULL, --条件,可以为空,不用填 where
@OrderField nvarchar(200) = NULL, --排序,可以为空,为空默认按主键升序排列,不用填 order by
@PageNo int = 1, --当前页,1表示第1页
@PageSize int = 10 --每页记录数
)
AS----由参数降格到此处
declare @Group nvarchar(1000) --分组依据,可以为空,不用填 group by
--declare @doCount bit --是否返回记录统计,false为否,true为是
set @Group=NULL
--set @doCount=0----如果@doCount传递过来的不是0,就执行总数统计
--IF (@doCount != 0)
--BEGIN
--DECLARE @strSQL nvarchar(4000) SET @strSQL = ''
--IF (@WHERE != '')
--EXEC('select count(*) as Total from ' + @TableName + ' where '+@WHERE)
--ELSE
--EXEC('select count(*) as Total from ' + @TableName + '')
--END
--ELSE
BEGIN/**//*默认排序*/
IF @OrderField IS NULL OR @OrderField = ''
SET @OrderField = @FieldKey
DECLARE @OrderFieldTable varchar(100)
DECLARE @OrderFieldName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/**//*设定排序语句.*/IF CHARINDEX('DESC',@OrderField)>0
IF CHARINDEX(' ASC', @OrderField) > 0
IF CHARINDEX(' DESC', @OrderField) < CHARINDEX(' ASC', @OrderField)
SET @operator = '<='
ELSE
SET @operator = '>='
ELSE
SET @operator = '<='
ELSE
SET @operator = '>='SET @strSortColumn = REPLACE(REPLACE(REPLACE(@OrderField, ' ASC', ''), ' DESC', ''), ' ', '')
/**//********单字段排序********/
IF CHARINDEX(',', @strSortColumn) = 0
BEGIN
DECLARE @OT varchar(4)
IF CHARINDEX('desc',@OrderField)>0
SET @OT='DESC'
else
SET @OT='ASC'
SET @WHERE='WHERE '+@WHERE
EXEC [P_TopWinCMS_PageList_Old]
@tablename = @TableName,
@fields = @Fields,
@condition = @WHERE ,
@orderfield = @strSortColumn,
@ordertype = @OT,
@pagesize = @PageSize,
@PageNo = @PageNo RETURN
END/**//*******支持多字段排序********/DECLARE @pos1 INT, @pos2 INT
SET @pos1 = CHARINDEX(',', @strSortColumn)
IF @pos1 > 0
SET @strSortColumn = SUBSTRING(@strSortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @strSortColumn)
IF @pos2 > 0
BEGIN
SET @OrderFieldTable = SUBSTRING(@strSortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, LEN(@strSortColumn)-@pos2)
END
ELSE
BEGIN
SET @OrderFieldTable = @TableName
SET @OrderFieldName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @OrderFieldTable AND c.name = @OrderFieldNameIF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/**//*默认当前页*/
IF @PageNo < 1
SET @PageNo = 1
/**//*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNo - 1)*@PageSize + 1) AS varchar(50))
/**//*筛选以及分组语句.*/
IF @WHERE IS NOT NULL AND @WHERE != ''
BEGIN
SET @strFilter = ' WHERE ' + @WHERE + ' '
SET @strSimpleFilter = ' AND ' + @WHERE + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/**//*执行查询语句*/
EXEC(
'
DECLARE @OrderFieldColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @OrderFieldColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @OrderField + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator + ' @OrderFieldColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @OrderField + '
'
)
END
解决方案 »
- mysql安装1067错误
- mysql分表同步的问题
- 关于Mysql分区分表
- 存储过程的问题?
- 修改了innodb_data_home_dir后,mysql无法启动,请问该怎样解决?
- 100分求解mysql添加表中字段出现read only问题!!
- 急求 php+mysql+apache 的网络空间!! 在线等.......
- 怎样把Access数据库中的数据转移到MicrosoftSQL数据库中呀?
- phpmyadmin2.26 连接win2000下的MySQL 3.23.52-nt 出错帮忙看一下
- into outfile
- mysql安装完后,我修改了数据库保存路劲后,启动不了。
- mysql-cluster-server-5.1:
DROP procedure IF exists P_TopWinCMS_PageList;
CREATE procedure P_TopWinCMS_PageList(
TableName varchar(1000),
FieldKey varchar(100),
Fields varchar(1000) = '*',
WHER varchar(1000)
OrderField nvarchar(200) = NULL,
PageNo int = 1,
PageSize int = 10
)
label_pro:begin----由参数降格到此处
declare Group varchar(1000);
set @Group=NULL
BEGIN/**//*默认排序*/
SET @OrderFieldd=OrderField;
IF OrderField IS NULL OR length(OrderField) = 0 then
SET @OrderFieldd = FieldKey;
end if;
DECLARE OrderFieldTable varchar(100)
DECLARE OrderFieldName varchar(100)
DECLARE strSortColumn varchar(200)
DECLARE operator char(2)
DECLARE type varchar(100)
DECLARE prec int
/**//*设定排序语句.*/IF instr(@OrderFieldd,'DESC')>0
IF instr(@OrderFieldd,' ASC') > 0
IF instr( @OrderFieldd,' DESC') < instr(@OrderFieldd,' ASC') then
SET operator = '<=';
ELSE
SET operator = '>=' ;
end if;
ELSE
SET @operator = '<=';
end if;
ELSE
SET @operator = '>=';
end if;
SET @strSortColumn = REPLACE(REPLACE(REPLACE(@OrderFieldd, ' ASC', ''), ' DESC', ''), ' ', '');
/**//********单字段排序********/
IF instr(strSortColumn,',') = 0 then
BEGIN
IF instr(OrderField,'desc')>0 then
SET OT='DESC';
else
SET OT='ASC';
end if;
SET @WHERs=concat('WHERE ',WHER);
call P_TopWinCMS_PageList_Old(TableName,Fields,@WHERs,@strSortColumn,@OT,PageSize,PageNo);
leave label_pro;
END;
end if;
/**//*******支持多字段排序********/SET @pos1 = instr(@strSortColumn,',');
IF @pos1 > 0 then
SET @strSortColumn = SUBSTRING(@strSortColumn, 1, @pos1-1);
end if;
SET @pos2 = instr(@strSortColumn,'.');
IF @pos2 > 0 then
BEGIN
SET @OrderFieldTable = SUBSTRING(@strSortColumn, 1, @pos2-1);
IF @pos1 > 0 then
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, @pos1-@pos2-1);
ELSE
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, LENGTH(@strSortColumn)-@pos2);
end if;
END;
ELSE
BEGIN
SET @OrderFieldTable = TableName;
SET @OrderFieldName = strSortColumn;
END;
end if;
/////////////////
/////////////////
--到这里我不会了,这个好像在系统表里查询什么类型
/////////////////////
////////////////////
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @OrderFieldTable AND c.name = @OrderFieldNameIF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/**//*默认当前页*/
IF @PageNo < 1
SET @PageNo = 1
/**//*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNo - 1)*@PageSize + 1) AS varchar(50))
/**//*筛选以及分组语句.*/
IF @WHERE IS NOT NULL AND @WHERE != ''
BEGIN
SET @strFilter = ' WHERE ' + @WHERE + ' '
SET @strSimpleFilter = ' AND ' + @WHERE + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/**//*执行查询语句*/
EXEC(
'
DECLARE @OrderFieldColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @OrderFieldColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @OrderField + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator + ' @OrderFieldColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @OrderField + '
'
)
END
FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @OrderFieldTable AND c.name = @OrderFieldName
SELECT @typ:e=t.name, @prec:=c.prec
FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @OrderFieldTable AND c.name = @OrderFieldName
如果mysql set可以不确定类型的话,貌似就不用那么查询了。
但是我不知道mysql中频繁地set是否影响性能!
delimiter $$
DROP procedure IF exists P_TopWinCMS_PageList;
CREATE procedure P_TopWinCMS_PageList(
TableName varchar(1000),
FieldKey varchar(100),
Fields varchar(1000),
WHER varchar(1000),
OrderField nvarchar(200),
PageNo int,
PageSize int
)
label_pro:begin
if Fields is null or length(Fields)=0 then
set @Fieldss='*';
else
set @Fieldss=Fields;
end if;
if PageNo is null then
set @PageNo1=1;
else
set @PageNo1=PageNo;
end if;
if PageSize is null then
set @PageSize1=10;
else
set @PageSize1=PageSize;
end if;
set @Group=NULL;
SET @OrderFieldd=OrderField;
IF OrderField IS NULL OR length(OrderField) = 0 then
SET @OrderFieldd = FieldKey;
end if;
/**//*设定排序语句.*/ IF instr(@OrderFieldd,'DESC')>0 then
IF instr(@OrderFieldd,' ASC') > 0 then
IF instr( @OrderFieldd,' DESC') < instr(@OrderFieldd,' ASC') then
SET @operator = '<=';
ELSE
SET @operator = '>=' ;
end if;
ELSE
SET @operator = '<=';
end if;
ELSE
SET @operator = '>=';
end if;
SET @strSortColumn = REPLACE(REPLACE(REPLACE(@OrderFieldd, ' ASC', ''), ' DESC', ''), ' ', '');
/**//********单字段排序********/
IF instr(@strSortColumn,',') = 0 then
BEGIN
IF instr(@OrderFieldd,'desc')>0 then
SET @OT='DESC';
else
SET @OT='ASC';
end if;
SET @WHERs=concat('WHERE ',WHER);
call P_TopWinCMS_PageList_Old(TableName,@Fieldss,@WHERs,@strSortColumn,@OT,@PageSize1,@PageNo1);
leave label_pro;
END;
end if;
/**//*******支持多字段排序********/
SET @pos1 = instr(@strSortColumn,',');
IF @pos1 > 0 then
SET @strSortColumn = SUBSTRING(@strSortColumn, 1, @pos1-1);
end if;
SET @pos2 = instr(@strSortColumn,'.');
IF @pos2 > 0 then
BEGIN
SET @OrderFieldTable = SUBSTRING(@strSortColumn, 1, @pos2-1);
IF @pos1 > 0 then
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, @pos1-@pos2-1);
ELSE
SET @OrderFieldName = SUBSTRING(@strSortColumn, @pos2+1, LENGTH(@strSortColumn)-@pos2);
end if;
END;
ELSE
BEGIN
SET @OrderFieldTable = TableName;
SET @OrderFieldName = @strSortColumn;
END;
end if; /**//*默认当前页*/
IF @PageNo1 < 1 then
SET @PageNo1 = 1;
end if;
/**//*设置分页参数.*/
SET @strPageSize = @PageSize1;
SET @strStartRow = (@PageNo1 - 1)*@PageSize1 + 1;
/**//*筛选以及分组语句.*/
IF @WHERs IS NOT NULL AND lenth(@WHERs) >0 then
BEGIN
SET @strFilter = concat(' WHERE ', @WHERs, ' ');
SET @strSimpleFilter = concat(' AND ' , @WHERs , ' ');
END;
ELSE
BEGIN
SET @strSimpleFilter = '';
SET @strFilter = '';
END;
end if;
IF @Group IS NOT NULL AND length(@Group) >0 then
SET @strGroup = concat(' GROUP BY ' , @Group ,' ');
ELSE
SET @strGroup = '';
end if;
/**//*执行查询语句*/
begin
set @OrderFieldColumn='';
set @sql=concat('SET ROWCOUNT ', @strStartRow,'
SELECT ',@strSortColumn + ' into ',@OrderFieldColumn,' FROM ' , TableName , @strFilter , ' ' , @strGroup , ' ORDER BY ' , @OrderFieldd);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @sql1=concat('SET ROWCOUNT ',@strPageSize,'
SELECT ' , @Fields , ' FROM ' , TableName , ' WHERE ' , @strSortColumn , @operator , @OrderFieldColumn , @strSimpleFilter , ' ' , @strGroup , ' ORDER BY ' , @OrderField,'set @RetCount = @@ROWCOUNT' );
PREPARE stmt1 FROM @sql1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
end;
END;$$被我改成这样了,写是写进去了,但不知道执行起来是否会错误,好心人帮我看下。