Using Temporary Tables in SQL server

So what if you wish to select data rom a table in SQL server whilst running a store dprocedure in a web application for multiple users? you don't want SQL server to throw an error when it tries to create the same table twice (or more times) during user sessions. there is a nifty way to create temporary tables existing only in the current user's session on the fly...

The following script section from a stored procedure I wrote selects data from a Friends table into two temporary tables called first_friendset and second_friendset (e.g. the line SELECT userid1 INTO #first_friendset).

When SQL-Server sees the hashtag (#), it realises that these are temporary tables and creates those tables only for the current user's session.

finally, you should drop the tables after use to clean up

/* check username was supplied */            IF ( @UserName = null )
                BEGIN
                GOTO Cleanup            
            END 
   
    /* get user id */    SELECT @thisUserId = UserId FROM aspnetdb.dbo.aspnet_Users WHERE UserName = @UserName
       
    /*... Security - User exists? */    IF ( NOT EXISTS ( SELECT userid
                  FROM   aspnetdb.dbo.aspnet_Users
                  WHERE  userid = @UserID ) )
    BEGIN
        GOTO Cleanup
    END
    
    SELECT userid1 INTO #first_friendset
        FROM dbo.Friends
            WHERE  userid2 = @thisUserId
                 
    SELECT userid2 INTO #second_friendset
        FROM dbo.Friends
            WHERE  userid1 = @thisUserId
   
    /* append */   
    INSERT INTO #first_friendset (userid1)
    SELECT userid2 FROM #second_friendset a
          WHERE NOT EXISTS (
              SELECT * FROM #first_friendset b
                 WHERE a.userid2 = b.userid1
                 )
    SELECT @FriendsCount = COUNT (userid1) FROM #first_friendset
   
    /* Fill in profiles */
    SELECT #first_friendset.userid1, aspnetdb.dbo.aspnet_Users.UserName, @FriendsCount AS friendscount
    FROM #first_friendset
    INNER JOIN aspnetdb.dbo.aspnet_Users ON #first_friendset.userid1 = aspnetdb.dbo.aspnet_Users.UserID
       
    DROP TABLE #first_friendset
    DROP TABLE #second_friendset