USE [FadmadDB]
GO
/****** 对象: StoredProcedure [dbo].[p_GetTableSubMemberList] 脚本日期: 06/02/2010 15:58:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.sp_BusinessInfo_GetMemberList Script Date: 2006-3-13 16:03:34 ******/
ALTER PROCEDURE [dbo].[p_GetTableSubMemberList]
(
@ColList nvarchar(4000)='', --显示字段
@Table nVARCHAR(4000)='', --查询的表
@Where nvarchar(4000)='', --查询条件
@Sort VARCHAR(255)='', --排序条件
@PageSize int=0, --每页记录数
@PageIndex int=0, --当前页码
@ID VARCHAR(255)='', --ID字段
@ID_WithTableName nVARCHAR(255)='',--带有表名前缀的ID字段名
@DoCount bit=0 , --是否获取记录数
@TableName nvarchar(4000)='', --待关联的表
@ConnectList nvarchar(200) -- 待关联的列
)
AS
--if((SELECT PATINDEX('%select%', @Where))>0)
--RETURN
if((SELECT PATINDEX('%UPDATE %', @Where))>0)
RETURN
if((SELECT PATINDEX('% UPDATE %', @Where))>0)
RETURN
if((SELECT PATINDEX('%DELETE%', @Where))>0)
RETURN
if((SELECT PATINDEX('%EXEC %', @Where))>0)
RETURN
if((SELECT PATINDEX('%;%', @Where))>0)
RETURN
if((SELECT PATINDEX('%create %', @Where))>0)
RETURN
if((SELECT PATINDEX('% create %', @Where))>0)
RETURN
if((SELECT PATINDEX('%insert %', @Where))>0)
RETURN
if((SELECT PATINDEX('% insert %', @Where))>0)
RETURN
if((SELECT PATINDEX('%drop %', @Where))>0)
RETURN
if((SELECT PATINDEX('% drop %', @Where))>0)
RETURN
if((SELECT PATINDEX('% alter %', @Where))>0)
RETURN
if((SELECT PATINDEX('%alter %', @Where))>0)
RETURN
if((SELECT PATINDEX('%xp_%', @Where))>0)
RETURN
if((SELECT PATINDEX('%chr %', @Where))>0)
RETURN
if((SELECT PATINDEX('% chr %', @Where))>0)
RETURN
if((SELECT PATINDEX('%mid %', @Where))>0)
RETURN
if((SELECT PATINDEX('% mid %', @Where))>0)
RETURN
if((SELECT PATINDEX('%master %', @Where))>0)
RETURN
if((SELECT PATINDEX('% master %', @Where))>0)
RETURN
if((SELECT PATINDEX('%truncate %', @Where))>0)
RETURN
if((SELECT PATINDEX('% truncate %', @Where))>0)
RETURN
if((SELECT PATINDEX('%char %', @Where))>0)
RETURN
if((SELECT PATINDEX('% char %', @Where))>0)
RETURN
if((SELECT PATINDEX('%ASCII %', @Where))>0)
RETURN
if((SELECT PATINDEX('% ASCII %', @Where))>0)
RETURN
if((SELECT PATINDEX('%cmd %', @Where))>0)
RETURN
if((SELECT PATINDEX('% cmd %', @Where))>0)
RETURN
if((SELECT PATINDEX('%cmd%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20cmd%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%UPDATE%20', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20UPDATE%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%EXEC%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%create%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20create%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%insert%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20insert%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%drop%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20drop%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20alter%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%alter%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%xp_%', @Where))>0)
RETURN
if((SELECT PATINDEX('%chr%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20chr%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%mid%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20mid%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%master%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20master%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%truncate%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20truncate%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%char%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20char%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%ASCII%20%', @Where))>0)
RETURN
if((SELECT PATINDEX('%%20ASCII%20%', @Where))>0)
RETURN
DECLARE @Sql nVARCHAR(4000)
if(@Where='')
set @Where='1=1'
if(@DoCount=1)
begin
if(@ID_WithTableName='' OR @ID_WithTableName IS NULL)
set @ID_WithTableName=@ID
set @Sql='select count('+@ID_WithTableName+') as countint from '+@Table+' where '+@Where
end
else
Begin
if @TableName=''
--SET @Sql='select '+@ColList+' from '+ @Table +' where '+@Where
SET @Sql ='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@ColList+' FROM '+@Table+' WHERE '+@Table+'.'+@ID+' NOT IN
(SELECT TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@Table+' WHERE '+@Where+' ORDER BY '+@Sort+' ) AND '+@Where+' ORDER BY '+@Sort
else
SET @Sql ='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@ColList+' FROM '+@Table+' left join '+@TableName+' on '+@TableName+'.'+@ConnectList+' = '+@Table+'.'+@ConnectList+' WHERE '+@Table+'.'+@ID+' NOT IN
(SELECT TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@Table+' WHERE '+@Where+' ORDER BY '+@Sort+' ) AND '+@Where+' ORDER BY '+@Sort
End
EXEC(@Sql)
本文转自火地晋博客园博客，原文链接：http://www.cnblogs.com/yelaiju/archive/2010/06/02/1750058.html，如需转载请自行联系原作者
