【图片复制粘贴效果更清楚】
ALTER PROCEDURE [dbo].[usp_apiUser]
@Account varchar(50)='',
@BeginTime varchar(50)='',--yyyy/MM/dd
@EndTime varchar(50)='',
@pageNumber int=1,
@pageSize int=10,
@phone varchar(30)=''
AS
BEGIN
declare @SQL varchar(max)
declare @tmp int
select @tmp=@pageSize*(@pageNumber-1)
select @SQL='select top ('+cast(@pageSize as varchar)+')*from
(
select ROW_NUMBER() over(order by Isid) as RowId, * from dbo.Api_User
) a where RowId >'+cast(@tmp as varchar)
if(isnull(@BeginTime,'') <>'' and isnull(@Account,'') ='' and isnull(@Phone,'') ='' and isnull(@EndTime,'')<>'')
select @SQL=@SQL+'and CONVERT(VARCHAR,RegisterTime,112) between '''+CONVERT(VARCHAR,@BeginTime,112)+''' and '''+CONVERT(VARCHAR,@EndTime,112)+''''
else if(isnull(@BeginTime,'')='' and isnull(@Account,'')<>'' and isnull(@Phone,'')='' and isnull(@EndTime,'')='')
select @SQL=@SQL+' and Account='''+cast(@Account as varchar)+''' '
else if(@BeginTime='' and @Account='' and @Phone <> '' and @EndTime='')
select @SQL=@SQL+' and Phone='''+cast(@Phone as varchar)+''' '
else if(@BeginTime<>'' and @Account <>'' and @Phone='' and @EndTime <>'')
select @SQL=@SQL+'and CONVERT(VARCHAR,RegisterTime,112) between '''+CONVERT(VARCHAR,@BeginTime,112)+''' and '''+CONVERT(VARCHAR,@EndTime,112)+'''and Account='''+cast(@Account as varchar)+''''
else if(@BeginTime<>'' and @Account='' and @Phone <>'' and @EndTime <>'')
select @SQL=@SQL+'and CONVERT(VARCHAR,RegisterTime,112) between '''+CONVERT(VARCHAR,@BeginTime,112)+''' and '''+CONVERT(VARCHAR,@EndTime,112)+'''and Phone='''+cast(@Phone as varchar)+''''
else if(@BeginTime ='' and @Account <>'' and @Phone <>'' and @EndTime='')
select @SQL=@SQL+'and Account='''+cast(@Account as varchar)+'''and Phone='''+cast(@Phone as varchar)+''' '
else if(@BeginTime <>'' and @Account <>'' and @Phone <>'' and @EndTime <>'')
select @SQL=@SQL+'and CONVERT(VARCHAR,RegisterTime,112) between '''+CONVERT(VARCHAR,@BeginTime,112)+''' and '''+CONVERT(VARCHAR,@EndTime,112)+'''and Account='''+cast(@Account as varchar)+'''and Phone='''+cast(@Phone as varchar)+''''
else
select @SQL=@SQL
print @SQL
exec(@SQL)
END
一定要加print @SQL exec(@SQL)输出存储过程的结果，前台调用存储过程在可以看见
declare @SQL varchar(max)：定义一个变量类型大小。
declare @tmp int select @tmp=@pageSize*(@pageNumber-1):定义一个int类型的变量处理参数运算的结果。
select @SQL='select top ('+cast(@pageSize as varchar)+')*from
(
select ROW_NUMBER() over(order by Isid) as RowId, * from dbo.Api_User
) a where RowId >'+cast(@tmp as varchar)：SQL语句的主体
-------这里是拼接的主体举一个例子
select @SQL=@SQL+'and CONVERT(VARCHAR,RegisterTime,112) between '''+CONVERT(VARCHAR,@BeginTime,112)+''' and '''+CONVERT(VARCHAR,@EndTime,112)+''''
@SQL就是主体语句，加上后面的条件就组成一条完整的分页SQL;
那么为什么要加上3个引号【''' '''】前面的单引号是为了拼接条件而加的，加三个引号是在条件的参数上加的，如果不加引号或少加引号那么参数会变成int类型超出长度就会溢出加上引号就会变成字符串类型定义好字符串的长度就可以避免溢出问题；
<>''就是不等于空
='’就是等于空
CONVERT(VARCHAR,@BeginTime,112)对之间的格式进行截取转换只要年月日 【详细可百度】
cast(@pageSize as varchar) 数据库的类型转换
