Results 1 to 6 of 6

Thread: temporary table

  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Unanswered: temporary table

    Hi

    I am using SQL 2000 I have the following code. When saving it, I am getting an error:
    There is already an object named '##tbl' in the database

    This is although #tbl is dropped.
    Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.

    declare @x int
    set @x=1
    IF @x=0
    begin
    create table #tbl (
    abc int
    )
    drop table #tbl
    end

    IF @x=1
    begin
    create table #tbl (
    abc int
    )
    drop table #tbl
    end

    Than you.

  2. #2
    Join Date
    Jan 2004
    Posts
    40
    it looks like the code is doing what you've coded

    the if@x=0 block will never run, so you never drop any #tbl table

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    though i don't see where you're going with this, but the code below will do the same thing (whatever thing you're trying to do) without resulting in error:

    declare @x int
    set @x=1
    create table #tbl (abc int)
    IF @x=0 drop table #tbl
    IF @x=1 drop table #tbl

  4. #4
    Join Date
    Jan 2004
    Posts
    4

    Unhappy

    Thanks for the replies.
    This code is part of a stored procedure. I am getting the error when trying to SAVE the procedure. the @x variable is supposed to be a parameter to the procedure.

    CREATE PROCEDURE MY_TEST
    @x int
    AS
    IF @x=0
    begin
    create table #tbl (
    abc int
    )
    drop table #tbl
    end

    IF @x=1
    begin
    create table #tbl (
    abc int
    )
    drop table #tbl
    end

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    ok, but as i demonstrated earlier, your code can be rewritten without changing the logic:

    Code:
    create procedure my_test (
       @x int )
    as
       create table #tbl (abc int)
       if @x=0 drop table #tbl
       if @x=1 drop table #tbl
    i just don't understand what you're trying to do with it. can you shed some light here?

  6. #6
    Join Date
    Jan 2004
    Posts
    4

    Talking

    thanks. simple but effective! this would work.

    this procedure is actually creating a temp table for another procedure to process. the 2nd procedure has to be called multiple times. i ommited from the sample most of the code, and left just the part which shows the create table ... drop table. sorry if it was confusing.

    thansk again for the advise!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •