Here's my cursor:
DECLARE @total int, @city varchar(64), @state varchar(16), @table char(64) DECLARE city_cursor CURSOR FOR SELECT DISTINCT City, State FROM MyAddressTable ORDER BY City SET NOCOUNT ON CREATE TABLE #City_Count_Temp ( City VARCHAR(64), State VARCHAR(16), Total INT ) OPEN city_cursor FETCH NEXT FROM city_cursor INTO @city, @state WHILE @@FETCH_STATUS = 0 BEGIN SELECT @total = (SELECT COUNT(City) FROM MyAddressTable WHERE City = @city and State = @state) --SELECT @city AS City, @state AS State, @Total AS Total
It's actually a pretty simple script. The purpose of the script is to count the occurrences of each city in the table and then output it into a nice pretty list. It accomplishes this by first querying the table for the unique list of City/State combinations. Then using this list it again curses through the table counts the number of occurrences of each and dumps the information to a temporary table. Finally, there's a simple query of the temporary table that produces the final list.INSERT INTO #City_Count_Temp VALUES (@city, @state, @total) FETCH NEXT FROM city_cursor INTO @city, @state END CLOSE city_cursor DEALLOCATE city_cursor SET NOCOUNT OFF SELECT * FROM #City_Count_Temp DROP TABLE #City_Count_Temp
So the bumps in the road that I couldn't quite figure out... First, the only way I could get the script to make a pretty list was write the data to a temporary table. You might have noticed a commented out line in the middle. Originally, this was how the script provided the output without writing to the temp table. The problem was that SQL Management Studio displayed a separate result for each curse of the script. If there's another way to do this, I'd love to know!
Also, I tried to set the table name (MyAddressTable) as a variable but for some reason, it would error every time saying that I needed to declare the variable (even though I had declared the variable).
So that's my little cursor. I'm happy to answer any questions about it. I'm also happy to receive any suggestions about how to improve it