Accessing runtime created table

[PravinBand]

Hi,

Can somebody tell me why following is happening?

When I am executing following proc with paramter 1,
it is runnig fine.
But when I am giving parameter 2, it is giving me following error message:

Table 'cas_t' can not found.


Stored Procedure

create proc sp2 (@proc int)
as
begin
if @proc = 1
begin
select * into cas_t from cas
end

if @proc = 2
begin
create index ix1 on cas_t(lacct15)
select lacct15,count(*) from cas_t group by lacct15
end
end

 

[nikhilparchure]

Firstly after executing the procedure with 1 parameter
have u checked if the table has got created .And if yes then only exec the procedure using the 2 parameter

Also u could modify the procedure, as below

create proc sp2 (@proc int)
as
begin
if @proc = 1
begin
if Exists ( Select 1
from sysobjects
where name like 'cas_t'
and type like 'U'
)
Begin
Select 'Table already present .Cannot use insert into'
End
Else
Begin
select * into cas_t from cas
End

end

if @proc = 2
begin
if Exists ( Select 1
from sysobjects
where name like 'cas_t'
and type like 'U'
)
Begin
create index ix1 on cas_t(lacct15)
select lacct15,count(*) from cas_t group by lacct15
End
Else
Begin
Select 'Table not found ,Cannot create ....index'
End
end
end

 

[PravinBand]

The funny part is after executing first part (with parameter 1), Table is got created, but still second part (with parameter 2) does not able to find it.

 

[nikhilparchure]

pls try to run my procedure and pls u tell me the results..

I am det sure that yor are ssing someting

Parchure Nikhil

 

[PravinBand]

Nikhil,

Your proc is behaving exactly same what my proc does. Another solution I found that to use dynamic SQL.

Thanks for your kind help.

Praveen