SQL Server Performance

Temp Table in Stored Procedure

Discussion in 'SQL Server 2005 General Developer Questions' started by TedManowar, Mar 19, 2007.

  1. TedManowar New Member

    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
  2. khtan New Member

    change to


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



    KH
  3. TedManowar New Member

    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.
  4. Adriaan New Member

    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.
  5. TedManowar New Member

    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
  6. Adriaan New Member

    The error message was telling you exactly what was going wrong, though.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. ndinakar Member

    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
    ***********************
  8. DilliGrg Member

    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))




    quote:Originally posted by TedManowar

    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



    Thanks,

    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  9. Adriaan New Member

    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=';)' />]
  10. Roji. P. Thomas New Member

Share This Page