USE [Emergency] GO /****** 对象: StoredProcedure [aqyjw].[UP_GetRecordByPage] 脚本日期: 07/22/2010 12:05:46 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [aqyjw].[UP_GetRecordByPage] @TableNames VARCHAR(500), --表名,可以是多个表,但不能用别名 @PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空 @Fields VARCHAR(2000), --要取出的字段,可以是多个表的字段,可以为空,为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页,0表示第1页 @Filter VARCHAR(4000) = '*', --条件,可以为空,不用填 where @Group VARCHAR(500) = '', --分组依据,可以为空,不用填 group by @Order VARCHAR(500) = '' , --排序,可以为空,为空默认按主键升序排列,不用填 order by @IsReCount bit = 0 , -- 返回记录总数, 非 0 值则返回 @Total int output AS If @isReCount = 0 BEGIN DECLARE @SortColumn VARCHAR(500) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(500) DECLARE @SortName VARCHAR(500) IF @Fields = '' SET @Fields = '*' IF @Filter = '' SET @Filter = 'WHERE 1=1' ELSE SET @Filter = 'WHERE ' + @Filter IF @Group <> '' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = UPPER(@Order) IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortTable = @TableNames SET @SortColumn = @PrimaryKey SET @SortName = @SortColumn SET @pos1 = CHARINDEX('.', @SortName) IF @pos1 > 0 BEGIN SET @SortTable = SUBSTRING(@SortName, 1, @pos1-1) SET @SortName = SUBSTRING(@SortName, @pos1 + 1, LEN(@SortName)) END
SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM dbo.sysobjects o JOIN dbo.syscolumns c on o.id=c.id JOIN dbo.systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' if charindex('numeric',@type)>0 SET @type = @type + '(5,3)' DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 Declare @StrSQL NVarChar(4000) Set @StrSQL = 'DECLARE @SortColumnBegin ' + CAST(@type AS VARCHAR) + ' SET ROWCOUNT ' + CAST(@TopRows AS varchar) +' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + CAST(@PageSize AS VARCHAR) + ' SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + ' ' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ' Exec(@StrSQL) --Print(@StrSQL)ENDIf @isReCount ! = 0 BEGIN declare @strSQL_num varchar(2000) set @strSQL_num = 'select ''@Total''= count(1) FROM ' + @TableNames + ' Where ' + @Filter + ' ' Exec(@strSQL_num) --print @strSQL_num END
select dbid,[name]
from master..sysdatabases
where [name]='mssqlsystemresource'
--把执行语句帖出来。
更改了
GO
/****** 对象: StoredProcedure [aqyjw].[UP_GetRecordByPage] 脚本日期: 07/22/2010 12:05:46 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [aqyjw].[UP_GetRecordByPage]
@TableNames VARCHAR(500), --表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(2000), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT, --每页记录数
@CurrentPage INT, --当前页,0表示第1页
@Filter VARCHAR(4000) = '*', --条件,可以为空,不用填 where
@Group VARCHAR(500) = '', --分组依据,可以为空,不用填 group by
@Order VARCHAR(500) = '' , --排序,可以为空,为空默认按主键升序排列,不用填 order by
@IsReCount bit = 0 , -- 返回记录总数, 非 0 值则返回
@Total int output
AS
If @isReCount = 0
BEGIN
DECLARE @SortColumn VARCHAR(500)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(500)
DECLARE @SortName VARCHAR(500) IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'WHERE 1=1'
ELSE
SET @Filter = 'WHERE ' + @Filter
IF @Group <> ''
SET @Group = 'GROUP BY ' + @Group IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = UPPER(@Order)
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN SET @SortTable = @TableNames
SET @SortColumn = @PrimaryKey
SET @SortName = @SortColumn SET @pos1 = CHARINDEX('.', @SortName)
IF @pos1 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortName, 1, @pos1-1)
SET @SortName = SUBSTRING(@SortName, @pos1 + 1, LEN(@SortName))
END
SET @Order = @SortColumn
SET @Operator = '>='
END DECLARE @type varchar(50)
DECLARE @prec int
SELECT @type=t.name, @prec=c.prec
FROM dbo.sysobjects o
JOIN dbo.syscolumns c on o.id=c.id
JOIN dbo.systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')' if charindex('numeric',@type)>0
SET @type = @type + '(5,3)' DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1 Declare @StrSQL NVarChar(4000)
Set @StrSQL = 'DECLARE @SortColumnBegin ' + CAST(@type AS VARCHAR) + '
SET ROWCOUNT ' + CAST(@TopRows AS varchar) +'
SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + CAST(@PageSize AS VARCHAR) + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + ' ' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
'
Exec(@StrSQL)
--Print(@StrSQL)ENDIf @isReCount ! = 0
BEGIN
declare @strSQL_num varchar(2000)
set @strSQL_num = 'select ''@Total''= count(1) FROM ' + @TableNames + ' Where ' + @Filter + ' '
Exec(@strSQL_num)
--print @strSQL_num
END
如图加select 权限
拒绝了对对象 'syscolumns' (数据库 'mssqlsystemresource',架构 'sys')的 SELECT 权限。(1 行受影响)(1 行受影响)
我直接在数据库里执行这个存储过程也报错。不管是用sa还是public角色的账号。都一样。
我先去吃个饭。