Create temp table in stored procedure -- name problem

Create temp table in stored procedure -- name problem

Post by jerem » Sat, 25 Aug 2001 09:15:30



My stored proc goes something like this:


  select stuff into #list from atable
  where etc.
  end
else begin
  select differentstuff into #list from atable
  where different criteria
end

The problem is that when I try to save this proc, I get a message saying
that #list already exists in this procedure.  However, if I change the name
in the 2nd begin block, it saves fine.

Obviously, I want the table to have the same name regardless of which
begin-end block executes.  What can I do?

Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by lindawi » Sat, 25 Aug 2001 10:21:37


Jeremy,

One way to handle this is to create a separate stored procedure for each
selection and then just execute one or the other in your main procedure. In
the long run this might be a better way to go because it provides more
flexibility. You can change or rewrite one procedure without wading through
the code of the other. You could also execute each procedure independently.

create procedure ProcA as
    select stuff into #list from atable
    where etc.
go
create procedure ProcB as
    select differentstuff into #list from atable
    where different criteria
go


        exec ProcA
    end
    else begin
        exec ProcB
    end
go

Linda


> My stored proc goes something like this:


>   select stuff into #list from atable
>   where etc.
>   end
> else begin
>   select differentstuff into #list from atable
>   where different criteria
> end

> The problem is that when I try to save this proc, I get a message saying
> that #list already exists in this procedure.  However, if I change the
name
> in the 2nd begin block, it saves fine.

> Obviously, I want the table to have the same name regardless of which
> begin-end block executes.  What can I do?

> Jeremy


 
 
 

Create temp table in stored procedure -- name problem

Post by Rich Dillo » Sat, 25 Aug 2001 10:28:48


Jeremy,

You could perform that logic in the WHERE clause.  Something like:

select stuff into #list from atable
where


Or you could create the temporary table first and then have multiple INSERT

Hope that helps,
- Rich


> My stored proc goes something like this:


>   select stuff into #list from atable
>   where etc.
>   end
> else begin
>   select differentstuff into #list from atable
>   where different criteria
> end

> The problem is that when I try to save this proc, I get a message saying
> that #list already exists in this procedure.  However, if I change the
name
> in the 2nd begin block, it saves fine.

> Obviously, I want the table to have the same name regardless of which
> begin-end block executes.  What can I do?

> Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by Steve Kas » Sat, 25 Aug 2001 10:53:41


You can select into tables of different names, and add an exec sp_rename
statement.
I don't know how to rename a temporary table, but if you aren't using
temporaries in production,
check this out:



else begin
  select 1 as c1, 2 as c2 into temporax from pubs..authors
  exec sp_rename 'temporax', 'tempora'
end
go

exec temp001 0
select * from tempora
drop table tempora
exec temp001 1
select * from tempora
drop table tempora

Steve Kass
Drew University


> Jeremy,

> You could perform that logic in the WHERE clause.  Something like:

> select stuff into #list from atable
> where


> Or you could create the temporary table first and then have multiple INSERT
> INTO statements.  If you may be selecting from different tables depending on

> Hope that helps,
> - Rich



> > My stored proc goes something like this:


> >   select stuff into #list from atable
> >   where etc.
> >   end
> > else begin
> >   select differentstuff into #list from atable
> >   where different criteria
> > end

> > The problem is that when I try to save this proc, I get a message saying
> > that #list already exists in this procedure.  However, if I change the
> name
> > in the 2nd begin block, it saves fine.

> > Obviously, I want the table to have the same name regardless of which
> > begin-end block executes.  What can I do?

> > Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by JHG » Sat, 25 Aug 2001 12:58:05


Linda, This looks just like I'd want it to be, but I never would have
guessed it would work.  For one thing, I thought local temporary tables (#)
just had a life of one call of a proc & therefore wouldn't survive the "go".

Also, I actually need my stored proc parameters in the procA, procB steps.
I presume I can pass parms into Proc A from Proc Main, eg:

  create procedure ProcA


 as
    select stuff into #list from atable
    where etc.
go
create procedure ProcB as


    select differentstuff into #list from atable
    where different criteria
go



    end
    else begin

    end
go

Thanks!

Jeremy


Jeremy,

One way to handle this is to create a separate stored procedure for each
selection and then just execute one or the other in your main procedure. In
the long run this might be a better way to go because it provides more
flexibility. You can change or rewrite one procedure without wading through
the code of the other. You could also execute each procedure independently.

create procedure ProcA as
    select stuff into #list from atable
    where etc.
go
create procedure ProcB as
    select differentstuff into #list from atable
    where different criteria
go


        exec ProcA
    end
    else begin
        exec ProcB
    end
go

Linda


> My stored proc goes something like this:


>   select stuff into #list from atable
>   where etc.
>   end
> else begin
>   select differentstuff into #list from atable
>   where different criteria
> end

> The problem is that when I try to save this proc, I get a message saying
> that #list already exists in this procedure.  However, if I change the
name
> in the 2nd begin block, it saves fine.

> Obviously, I want the table to have the same name regardless of which
> begin-end block executes.  What can I do?

> Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by JHG » Sat, 25 Aug 2001 13:00:00


Linda, This looks just like I'd want it to be, but I never would have
guessed it would work.  For one thing, I thought local temporary tables (#)
just had a life of one call of a proc & therefore wouldn't survive the "go".

Also, I actually need my stored proc parameters in the procA, procB steps.
I presume I can pass parms into Proc A from Proc Main, eg:

  create procedure ProcA


 as
    select stuff into #list from atable
    where etc.
go
create procedure ProcB as


    select differentstuff into #list from atable
    where different criteria
go



    end
    else begin

    end
go

Thanks!

Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by lindawi » Sat, 25 Aug 2001 15:39:15


Jeremy,

Quote:> I thought local temporary tables (#)
> just had a life of one call of a proc & therefore wouldn't survive the

"go".

The scope of a temp table is the procedure that creates it and any
procedures that are then executed from within the creating procedure. If you
create #list in ProcA and then execute a ProcA1 from within ProcA, #list is
visible to ProcA1. #list goes out of scope when ProcA completes, so it is
not visible to ProcMain. So if another procedure needs to work with #list,
then ProcA needs to call that procedure, not ProcMain.

Quote:> Also, I actually need my stored proc parameters in the procA, procB steps.
> I presume I can pass parms into Proc A from Proc Main, eg:

Yes, you can pass parameters from one procedure to the next exactly as you
have shown  below. You can also declare parameters as OUTOUT to return
values to ProcMain.

Linda

>   create procedure ProcA


>  as
>     select stuff into #list from atable
>     where etc.
> go
> create procedure ProcB as


>     select differentstuff into #list from atable
>     where different criteria
> go



>     end
>     else begin

>     end
> go

> Thanks!

> Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by JHG » Sun, 26 Aug 2001 12:42:36


Linda, I really appreciate your help on this.

I am still a bit puzzled, though.  Your example includes the following,
where my #list table is created in Proc A or Proc B.  I then want to use
#list for further steps, but if I understand correctly, the table vanishes
when either Proc A or B terminate.  Perhaps I need to create the #list table
before the if statement ...




     end
     else begin

     end

  do some more stuff with #list ...

Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by Doo » Sun, 26 Aug 2001 22:25:46


Don't know if Linda is around on the weekend :0  That's correct...local temp
tables go out of scope with the Proc it was created in.  So create it in
ProcMain and it will be available until ProcMain terminates (or you drop
it).

--
Doo
~~~~~~~~~~~~~~~~
Sr. Data Architect / DBA
PlanetJam Media Group

~~~~~~~~~~~~~~~~

> Linda, I really appreciate your help on this.

> I am still a bit puzzled, though.  Your example includes the following,
> where my #list table is created in Proc A or Proc B.  I then want to use
> #list for further steps, but if I understand correctly, the table vanishes
> when either Proc A or B terminate.  Perhaps I need to create the #list
table
> before the if statement ...




>      end
>      else begin

>      end

>   do some more stuff with #list ...

> Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by lindawi » Mon, 27 Aug 2001 02:30:55


Jeremy,

Now I'm a bit puzzled. :) I assumed from the pseudo-code you originally
posted, shown here, that 'differentstuff' meant different columns, thus my
suggestion to break it out into different procedures.


>   select stuff into #list from atable
>   where etc.
>   end
> else begin
>   select differentstuff into #list from atable
>   where different criteria
> end

If this is not the case and #list will always have the same structure, i.e.,
same number of columns with same names and same data types, then yes, you
can create it in ProcMain.

If 'differentstuff' does in fact mean different columns, then we've come
full circle and are back to the problem of needing two different create
table statements. You can get around this by creating #list in ProcMain with
a single column and then having two different ALTER TABLE statements to add
the columns you need before calling ProcA or ProcB.

Linda


> Linda, I really appreciate your help on this.

> I am still a bit puzzled, though.  Your example includes the following,
> where my #list table is created in Proc A or Proc B.  I then want to use
> #list for further steps, but if I understand correctly, the table vanishes
> when either Proc A or B terminate.  Perhaps I need to create the #list
table
> before the if statement ...




>      end
>      else begin

>      end

>   do some more stuff with #list ...

> Jeremy

 
 
 

Create temp table in stored procedure -- name problem

Post by jerem » Mon, 27 Aug 2001 03:59:53


Linda

#list will have the same columns, just the selection criteria is different.

I think I can do this now.  Appreciate all your help!

Jeremy


> Jeremy,

> Now I'm a bit puzzled. :) I assumed from the pseudo-code you originally
> posted, shown here, that 'differentstuff' meant different columns, thus my
> suggestion to break it out into different procedures.


> >   select stuff into #list from atable
> >   where etc.
> >   end
> > else begin
> >   select differentstuff into #list from atable
> >   where different criteria
> > end

> If this is not the case and #list will always have the same structure,
i.e.,
> same number of columns with same names and same data types, then yes, you
> can create it in ProcMain.

> If 'differentstuff' does in fact mean different columns, then we've come
> full circle and are back to the problem of needing two different create
> table statements. You can get around this by creating #list in ProcMain
with
> a single column and then having two different ALTER TABLE statements to
add
> the columns you need before calling ProcA or ProcB.

> Linda



> > Linda, I really appreciate your help on this.

> > I am still a bit puzzled, though.  Your example includes the following,
> > where my #list table is created in Proc A or Proc B.  I then want to use
> > #list for further steps, but if I understand correctly, the table
vanishes
> > when either Proc A or B terminate.  Perhaps I need to create the #list
> table
> > before the if statement ...




> >      end
> >      else begin

> >      end

> >   do some more stuff with #list ...

> > Jeremy

 
 
 

1. variable temp table name, a ~simple~ string problem in stored procedures

Within my stored procedure, I am using execute() so that I can build a
dynamic temp table name using a paramater value.
1)  Is there a better way to create this temp table - I need to
reference it back within my VB code.
2)  The string problem with this approach is that I need to convert
all my variables to string variables so that they can be used within
the string that gets built.  Is there an alternative?  I am not able
to get convert to work inline of the string.

I now have to do use 2 variables for each piece of data I want to use:




Thank you

John

2. Flailing in MSAccess

3. Temp table names in stored procedure

4. SP Internal Procs/subroutines?

5. Creating a table in a stored procedure using a parameter for the table name

6. Anyone Using FoxPro and Espia?

7. Create table with variable table name in stored procedure

8. How to copy archive redo logs onto Tape?

9. Creating a Temp table using results from a store procedure

10. Cannot Create temp table within Store procedure??

11. Creating a Temp table that is populated by a stored procedure

12. Stored Procedure to create temp table

13. using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure