SQL_游标写法

 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