neo8820


I wrote a stored procedure that does the following.

Selects records into a temp table.
If no records are in temp table after select I execute another stored procedure, drop the temp table and reselect records into the temp table.

I'm getting the following error and I don't think I should since I am dropping the temp table before creating it again.

Msg 2714, Level 16, State 1, Procedure sp_Alerts_GetFeedSubscriptions, Line 30
There is already an object named '#FeedSubscriptions' in the database.

When trying to execute the following sql

USE [ProblemTracker]
GO
/****** Object: StoredProcedure [dbo].[sp_Alerts_GetFeedSubscriptions] Script Date: 04/19/2007 14:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Alerts_GetFeedSubscriptions]
-- Add the parameters for the stored procedure here
@NBID char(7)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF Object_ID('tempdb..#FeedSubscriptions') IS NOT NULL
DROP TABLE #FeedSubscriptions

SELECT a.UID AS FeedUID INTO #FeedSubscriptions FROM Alerts_Feeds a, Alerts_UserFeeds b, Alerts_User c WHERE a.UID = b.AssociatedFeedUID AND b.AssociatedUserUID = c.UID AND c.NBID = @NBID

DECLARE @FeedSubscriptionsCount int
SET @FeedSubscriptionsCount = (SELECT COUNT(*) FROM #FeedSubscriptions)

IF (@FeedSubscriptionsCount = 0)
BEGIN
EXEC sp_Alerts_AddUserNew @NBID = @NBID
DROP TABLE #FeedSubscriptions

SELECT a.UID AS FeedUID INTO #FeedSubscriptions FROM Alerts_Feeds a, Alerts_UserFeeds b, Alerts_User c WHERE a.UID = b.AssociatedFeedUID AND b.AssociatedUserUID = c.UID AND c.NBID = @NBID
SET @FeedSubscriptionsCount = (SELECT COUNT(*) FROM #FeedSubscriptions)
END

IF (@FeedSubscriptionsCount <> 0)
BEGIN
SELECT * FROM #FeedSubscriptions
END

END



Re: Trouble with temp tables

neo8820


I changed some code around a little. Instead of dropping the table in the if statment and doing another select I did the following. It works however, I'd like to know why my original code didn't work since I don't see any reason why it wouldn't.

IF (@FeedSubscriptionsCount = 0)
BEGIN
EXEC sp_Alerts_AddUserNew @NBID = @NBID
TRUNCATE TABLE #FeedSubscriptions

INSERT INTO #FeedSubscriptions
SELECT a.UID AS FeedUID
FROM Alerts_Feeds a, Alerts_UserFeeds b, Alerts_User c
WHERE a.UID = b.AssociatedFeedUID AND b.AssociatedUserUID = c.UID AND c.NBID = @NBID
SET @FeedSubscriptionsCount = (SELECT COUNT(*) FROM #FeedSubscriptions)
END