Guest User

Untitled

a guest
Aug 20th, 2018
97
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.31 KB | None | 0 0
  1. SQL Cursor Processing Control Break logic
  2. DECLARE @CompanyName NVARCHAR(255)
  3. DECLARE @CompanyNameHold NVARCHAR(255)
  4. DECLARE @TESTCOUNT INT
  5. DECLARE @TOTALCOUNT INT
  6.  
  7. SELECT @CompanyNameHold = ''
  8. SELECT @TESTCOUNT = 0
  9. SELECT @TOTALCOUNT = 0
  10.  
  11. DECLARE ImportCursor CURSOR FOR
  12. SELECT [Company Name]
  13. FROM [MyDB].[dbo].[AccountsToImport]
  14. ORDER BY [Company Name]
  15.  
  16.  
  17. OPEN ImportCursor
  18. FETCH NEXT FROM ImportCursor INTO @CompanyName
  19.  
  20. WHILE @@FETCH_STATUS = 0
  21. BEGIN
  22.  
  23.  
  24. --Check HoldCompanyName = CompanyName
  25. IF (@CompanyName <> @CompanyNameHold)
  26. BEGIN
  27. --PROCESS NEW ACCOUNT
  28. IF @TESTCOUNT = 0 --PRIMER
  29. SET @CompanyNameHold = @CompanyName
  30. ELSE
  31. PRINT CAST(@TESTCOUNT AS NVARCHAR) + ' - ' + @CompanyNameHold
  32.  
  33. SET @TESTCOUNT = 1
  34. END
  35. ELSE
  36. BEGIN
  37. --ADD TO EXISTING
  38. SET @TESTCOUNT = @TESTCOUNT + 1
  39. END
  40.  
  41.  
  42. --Move CompanyName to HoldCompanyName
  43. SET @CompanyNameHold = @CompanyName
  44. SET @TOTALCOUNT = @TOTALCOUNT + 1
  45.  
  46. FETCH NEXT FROM ImportCursor INTO @CompanyName
  47. END
  48. --Process Last Record
  49. PRINT CAST(@TESTCOUNT AS NVARCHAR) + ' - ' + @CompanyNameHold
  50.  
  51. CLOSE ImportCursor
  52. DEALLOCATE ImportCursor
  53.  
  54. select [Company Name]
  55. , count(*) as RowsPerCompany
  56. from MyDB.dbo.AccountsToImport
  57. group by
  58. [Company Name]
Add Comment
Please, Sign In to add comment