澳门皇冠金沙网站▎在线官网
做最好的网站

SQL 查询上级存储过程

2020-03-02 作者:网络服务   |   浏览(172)

复制代码 代码如下:/* *@curentpage 当前页 *@pagesize 每页记录数 *@TableName 表名 *@key 主键 *@where 查询条件 1)空为 null 2)有查询条件不要带where *@order '0'表示 desc '1'是asc *@pageCount 总页数 */ create procedure Page @currentpage int,@pagesize int, @TableName varchar(30),@key varchar(30), @where varchar(50),@order varchar(1), @pageCount int ,@str varchar(450) output as begin ---------------执行的sql语句------------ declare @sql nvarchar(400),@ordreby nvarchar(200) declare @tempsql1 varchar(200),@tempsql2 varchar(200) ---------------记录总数----------------- declare @count int ---------------临时变量------------------------ declare @temp1 int,@temp2 int set @TableName=' '+@TableName+' ' set @key=' '+@key+' ' if @order='0' set @ordreby=' order by '+@key+'desc' else set @ordreby=' order by '+@key if @where='null' set @sql='select @count = count(*) from '+ @TableName else set @sql='select @count = count(*) from '+ @TableName+' where '+@where ------------@count 付值(声明变量@count 在说明是output 内型)--------------------------- exec sp_executesql @sql,N'@count int out',@count out ------------求总页数------------------------------ if (@count%@pagesize)=0 set @pagecount=@count/@pagesize else set @pagecount=@count/@pagesize+1 -----------判断显示当前页是否异常------------------ if @currentpage@pagecount set @currentpage=@pagecount if @currentpage1 set @currentpage=1 ----------记录数小于页面显示记录数----------------- if(@currentpage=1) begin if @where='null' set @where=' ' else set @where=' where '+@where set @sql = 'select top'+ str(@pagesize)+' * from '+@TableName+@where+@ordreby end else begin /**//* ---------------desc---------------------- *@temp1表示前面的记录 *@temp2表示后面的记录 *假设一共77个记录,每次取10个。取67~58,去掉前面的57个和后面的10个 */ if @order=0 begin set @temp1 = @count-@currentpage*@pagesize if @temp10 set @temp1=0 set @temp2 = (@currentpage - 1)*@pagesize if @where='null' begin set @tempsql1='select top ' + str(@temp1)+' '+@key+' from ' + @TableName+' order by ' +@key set @tempsql2='select top ' + str(@temp2)+' '+@key+' from ' + @TableName + @ordreby end else begin set @tempsql1='select top ' + str(@temp1)+' '+@key+' from ' + @TableName+' where '+@where+' order by ' +@key set @tempsql2='select top ' + str(@temp2)+' '+@key+' from ' + @TableName+' where '+@where+@ordreby end set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where '+@key+ ' not in ' set @sql= @sql+' ( '+ @tempsql1 +' ) and ' set @sql= @sql+@key+ ' not in ( '+@tempsql2 +' ) ' if @where='null' set @sql= @sql+@ordreby else set @sql= @sql+' and '+@where+@ordreby end /**//* ----------------asc--------------------- * @temp 表示前面显示的记录总数 * 去掉 @temp 在取出 pagesize 个即可 */ else begin set @temp1=(@currentpage-1)*@pagesize if @where='null' set @tempsql1='select top '+ str(@temp1)+' '+@key+' from ' + @TableName + @ordreby else set @tempsql1='select top '+ str(@temp1)+' '+@key+' from '

------------------------SQL 查询上级存储过程----------------------------

  • @TableName ++' where '+@where+@ordreby set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where '+@key+ ' not in ' set @sql=@sql+' ( '+@tempsql1+' ) ' if @where='null' set @sql= @sql+@ordreby else set @sql= @sql+' and '+@where+@ordreby end /**//* -------------------------------------*/ end set @str=@sql --exec sp_executesql @sql end GO

create proc proc_treeUpQuery
@id varchar(20)
as
declare @count int
declare @sql varchar(5000)
declare @temp varchar(2000)
declare @tempCount nvarchar(2000)
set @sql = 'select superiorid from b_userlist where userid = ' + @id
set @temp = 'select superiorid from b_userlist where userid = ' + @id
while (1=1)
begin
  set @tempCount = 'select @count=count(superiorid) from b_userlist where userid in (' + @temp + ')'
  exec sp_executesql @tempCount,N'@count int output',@count output
  if (@count=0)
   begin
    break
   end
  else
   begin
    set @temp = 'select superiorid from b_澳门皇冠金沙网站,在线官网,userlist where userid in (' + @temp + ')'
    set @sql = @sql + ' union ' + @temp
   end
end
exec(@sql)
go

本文由澳门皇冠金沙网站发布于网络服务,转载请注明出处:SQL 查询上级存储过程

关键词: