1 declare cursor_tbss cursor FOR
2 SELECT cus_no, cus_name, scd_name, COUNT(1) AS counts
3 FROM [CloudClearanceTest].[dbo].[a_feiyong]
4 GROUP BY cus_no, cus_name, scd_name
5
6 declare @cus_no nvarchar(500),
7 @cus_name nvarchar(500),
8 @scd_name NVARCHAR(500),
9 @counts INT
10
11 open cursor_tbss
12
13 fetch next from cursor_tbss into @cus_no, @cus_name, @scd_name, @counts
14 WHILE @@FETCH_STATUS = 0
15 BEGIN
16
17 DECLARE @c INT,
18 @customerId INT,
19 @addDept NVARCHAR(50)
20
21 SET @c = 0
22 SET @customerId=0
23 SET @addDept = ''
24
25 SELECT @customerId = id FROM CloudClearanceTest.dbo.CustomerInfo WHERE CustomerCode= @cus_no AND CustomerShortName=@cus_name AND Status = 1
26 SELECT @addDept = DepartNo FROM CloudClearanceTest.dbo.Department WHERE DepartNo LIKE '001097%' AND DepartName = @scd_name
27
28 SELECT @c = COUNT(1) FROM [CloudClearanceTest].dbo.FeeScale WHERE CustomerId = @customerId AND AddDept=@addDept AND Status=1
29
30 IF(@c <= 0)
31 BEGIN
32 INSERT INTO [CloudClearanceTest].dbo.FeeScale([CustomerId],[AddDept],[AddUserId],[AddUserName],[AddTime],[Status])
33 VALUES(@customerId, @addDept, '98910', '艾翌林', '2017-03-22 00:00:00', '1')
34 END
35 ELSE
36 BEGIN
37 PRINT @cus_no + ':' + @cus_name
38
39 END
40
41
42
43 fetch next from cursor_tbss into @cus_no, @cus_name, @scd_name, @counts
44 END
45
46 CLOSE cursor_tbss --关闭游标
47 DEALLOCATE cursor_tbss --释放游标
