SQL Server Performance Forum – Threads Archive

Temp Table in Stored Procedure

Hello, I am trying to make a temp table in a stored procedure and save some data into it.
This is the code: ALTER PROCEDURE dbo.TEST_REVENUE_PROFIT
(
@HotelList varchar(max)
)
AS
BEGIN
SET NOCOUNT ON CREATE TABLE #HotelRowElementCount
(
HotelID int,
RowElementCount int
) DECLARE @HotelID varchar(10), @Pos int, @RowCount int SET @HotelList = LTRIM(RTRIM(@HotelList))+ ‘,’
SET @Pos = CHARINDEX(‘,’, @HotelList, 1) IF REPLACE(@HotelList, ‘,’, ”) <> ”
BEGIN
WHILE @Pos > 0
BEGIN
SET @HotelID = LTRIM(RTRIM(LEFT(@HotelList, @Pos – 1)))
IF @HotelID <> ”
BEGIN SELECT fk_hotelID as HotelID, DATEDIFF(day,arrivaldate,departuredate) as RowElementCount
INTO #HotelRowElementCount
FROM Reservations
WHERE fk_hotelID = (CAST(@HotelID AS int))
END
SET @HotelList = RIGHT(@HotelList, LEN(@HotelList) – @Pos)
SET @Pos = CHARINDEX(‘,’, @HotelList, 1)
END
END SELECT *
FROM #HotelRowElementCount END
GO When I run this procedure, I get the following error which I cannot understand: Msg 2714, Level 16, State 1, Procedure TEST_REVENUE_PROFIT, Line 30
There is already an object named ‘#HotelRowElementCount’ in the database.Can anyone help ? Thanks in advance … TedManowar

---

change to
INSERT INTO #HotelRowElementCount
SELECT fk_hotelID as HotelID, DATEDIFF(day,arrivaldate,departuredate) as RowElementCount
INTO #HotelRowElementCount
FROM Reservations KH

---

I forgot to mention that there isn’t a table with the same name in the database, and that is the reason why I do not understand that message.

---

What khtan is pointing out is that the "SELECT … INTO …" syntax will create another table. So your script was trying to create the temp table twice. Use the "INSERT INTO … SELECT …" syntax to insert data into an existing (temp) table.

---

Thanks a lot for the quick reply, I thought that I could use the SELECT INTO clause. The error message was not very helpful anyways. Thanks again. TedManowar

---

The error message was telling you exactly what was going wrong, though.[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

Also, its a good idea to drop the table you created, explicitly. And, you might also want to look into table variables which reside in memory as opposed to temp table which is created in the tempdb. ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

You can still use SELECT INTO without first creating temp table explicitly because it creates one. You can also do this: IF object_id(‘tempdb..#HotelRowElementCount’) IS NOT NULL
DROP TABLE #HotelRowElementCount
SELECT fk_hotelID as HotelID, DATEDIFF(day,arrivaldate,departuredate) as RowElementCount
INTO #HotelRowElementCount
FROM Reservations
WHERE fk_hotelID = (CAST(@HotelID AS int))

Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Yes, but you should avoid using SELECT … INTO … anyway because it can cause blocking. Of course it’s easier to code it this way, rather than having to spell out the complete table definition, but at least you know what you’re doing.[<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />]

---

Well, thats just a myth. http://support.microsoft.com/?kbid=305977 http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

]]>