set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--
ALTER PROC [dbo].[Pipsaledepar]
@saleareaid INT,
@departmentid INT
AS
DECLARE @Count INT
SET @Count = 0
DECLARE @curId INT
DECLARE cur CURSOR --定义一个游标
READ_ONLY
FOR
SELECT StoreID FROM dbo.TBasStore WHERE SaleAreaID = @saleareaid
--指定游标
OPEN cur-- 打开游标
FETCH NEXT FROM cur INTO @curId --把提取操作的列数据放到局部变量中
WHILE ( @@fetch_status = 0 )--返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态
BEGIN
declare @StoreID int
select @StoreID = StoreID from dbo.TBasStore where StoreID=@curId
DECLARE @Count1 INT
SET @Count1 = 0
DECLARE @curId1 INT
DECLARE cur1 CURSOR --定义一个游标
READ_ONLY
FOR
SELECT UserID FROM dbo.TBasUserInfo WHERE DepartmentID = @departmentid
--指定游标
OPEN cur1-- 打开游标
FETCH NEXT FROM cur1 INTO @curId1 --把提取操作的列数据放到局部变量中
WHILE ( @@fetch_status = 0 )--返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态
BEGIN
declare @UserID int
select @UserID = UserID from dbo.TBasUserInfo where userid=@curId1
--插入门店和业务员关系
if not exists(select Userid,StoreID from TPlaAssignStore where UserID=@UserID and StoreID=@StoreID)
begin
insert into TPlaAssignStore(Userid,StoreID,RegionID,AssignTypeID)
values(@UserID,@StoreID,1,1)
end
SET @Count1 = @Count1 +1
--提前下一位信息
FETCH NEXT FROM cur1 INTO @curId1
END
SELECT @Count1
CLOSE cur1--关闭游标
DEALLOCATE cur1
--删除游标
SET @Count = @Count +1
--提前下一位信息
FETCH NEXT FROM cur INTO @curId
END
SELECT @Count
CLOSE cur--关闭游标
DEALLOCATE cur
--删除游标
set QUOTED_IDENTIFIER ON
go
--
ALTER PROC [dbo].[Pipsaledepar]
@saleareaid INT,
@departmentid INT
AS
DECLARE @Count INT
SET @Count = 0
DECLARE @curId INT
DECLARE cur CURSOR --定义一个游标
READ_ONLY
FOR
SELECT StoreID FROM dbo.TBasStore WHERE SaleAreaID = @saleareaid
--指定游标
OPEN cur-- 打开游标
FETCH NEXT FROM cur INTO @curId --把提取操作的列数据放到局部变量中
WHILE ( @@fetch_status = 0 )--返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态
BEGIN
declare @StoreID int
select @StoreID = StoreID from dbo.TBasStore where StoreID=@curId
DECLARE @Count1 INT
SET @Count1 = 0
DECLARE @curId1 INT
DECLARE cur1 CURSOR --定义一个游标
READ_ONLY
FOR
SELECT UserID FROM dbo.TBasUserInfo WHERE DepartmentID = @departmentid
--指定游标
OPEN cur1-- 打开游标
FETCH NEXT FROM cur1 INTO @curId1 --把提取操作的列数据放到局部变量中
WHILE ( @@fetch_status = 0 )--返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态
BEGIN
declare @UserID int
select @UserID = UserID from dbo.TBasUserInfo where userid=@curId1
--插入门店和业务员关系
if not exists(select Userid,StoreID from TPlaAssignStore where UserID=@UserID and StoreID=@StoreID)
begin
insert into TPlaAssignStore(Userid,StoreID,RegionID,AssignTypeID)
values(@UserID,@StoreID,1,1)
end
SET @Count1 = @Count1 +1
--提前下一位信息
FETCH NEXT FROM cur1 INTO @curId1
END
SELECT @Count1
CLOSE cur1--关闭游标
DEALLOCATE cur1
--删除游标
SET @Count = @Count +1
--提前下一位信息
FETCH NEXT FROM cur INTO @curId
END
SELECT @Count
CLOSE cur--关闭游标
DEALLOCATE cur
--删除游标