ALTER Proc [dbo].[p_GetServerDataCursor]

AS

BEGIN

   IF EXISTS ( SELECT  * FROM    sys.objects WHERE   object_id = OBJECT_ID(N'[dbo].[#ttableName]') AND type IN ( N'U' ) )

            BEGIN

                DROP TABLE [dbo].[#ttableName]

            END

    IF EXISTS ( SELECT  *

                    FROM    sys.objects

                    WHERE   object_id = OBJECT_ID(N'[dbo].[#tmpReturnData]')

                            AND type IN ( N'U' ) )

            BEGIN

                DROP TABLE [dbo].[#tmpReturnData]

            END

declare @orderNum varchar(255)

--创建临时表存储tagId

create table #ttableName(id int identity(1,1),Orders varchar(255))

--创建临时表返回数据

create table #tmpReturnData(id int identity(1,1),openid varchar(100),tagid varchar(100))

--定义循环变量，行数，总数

declare @n int,@rows int,@count varchar(1000),@a varchar(100)

--查询去重复tagId插入ttableName中

insert #ttableName(orders) select distinct tagId from Log_TagIdRecord where openId !='null' and IsServer=0

--row获取tagid行数    rowcount 返回受上一语句影响的行数。如果行数大于20 亿，请使用ROWCOUNT_BIG。

select @rows =@@rowcount

set @n=1

while @n<=@rows

begin

     --@count查询数量根据，ttableName表ID自增长根据@n循环每行数据

    select @count=COUNT( distinct openId) from Log_TagIdRecord where tagId=(select Orders from #ttableName where id=@n) and openId !='null'and IsServer=0;

     select @a=Orders from #ttableName where id=@n;

    IF(@count>49)

      BEGIN

        insert #tmpReturnData  select  distinct top 50 openId,tagId from Log_TagIdRecord where tagId=(select Orders from #ttableName where id=@n) and openId !='null'and IsServer=0;

     update  Log_TagIdRecord  set IsServer=1 where openId in (select openid from #tmpReturnData) and tagId in (select tagid from #tmpReturnData)

        break;

      END

     --@n增加

    select @n=@n+1

end

set @a='select * from #tmpReturnData'

EXEC (@a)

drop table #ttableName

drop table #tmpReturnData

END