Collaborative Cursing SQL Style

Earlier this week at work, I had some discretionary time. I'm not quite sure what inspired me but I decided I wanted to brush up on my SQL scripting skills.  I'm not by any means a SQL scripting pro but I know how to find the data that I'm looking for. In my line of work, I'm generally just looking to verify that the data being written to the database is correct. Generally a simple query will do the trick but occasionally more complex scripts, like a cursor or a loop, make it easier. Unfortunately, I couldn't remember how to write a cursor. Oops. In fact, I couldn't even remember what they were called. Oops again. But that's why I wanted to practice so that when I actually need to write one, it'll be easy. I finally figured it out with the help of Google but I still ran into a couple of bumps in the road. The end result does work but I welcome tips for improvement.

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

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

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.

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