SQL查询拼接存储过程 分页

【图片复制粘贴效果更清楚】

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)    数据库的类型转换