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
