volatile tables inside the stored procedure

Database
Enthusiast

volatile tables inside the stored procedure

Hello, 

I have the following two questions please:

1) While creating a volatile talbe inside a stored procedure : it needs to be created first outside the SP and then again inside. I am doing this for 2 tables. The errors are now coming up as warnings. I ignored them as i could read successfully from these tables. Anything i am missing and should be worried about? 

SPL5000:W(L16), E(3803):Table 'GLOBAL_VAR' already exists.

SPL5000:W(L145), E(3803):Table 'all_apps' already exists.

2) When I create the table outside the procedure, does it have to have the exact columns as the table I would be creating inside the procedure? I am wondering if i can create all_apps outside the SP with a basic simple query - create table all_apps as SELECT  '2013-01-01' AS report_start  and then have the  real and lengthier 50 line query inside the SP to create all_apps.

Thanks in advance. 

1 REPLY
Senior Supporter

Re: volatile tables inside the stored procedure

Hi SGIT,

 

you mention "1) While creating a volatile talbe inside a stored procedure : it needs to be created first outside the SP and then again inside."

Can you share an simple example code for your volatile table / stored procedure issue. I am not sure that I understand your problem.

The volatile table exits within a session. So if you created it already before you run the SP create statement you will get the errors you see. So I wouldn't expect that you have to create them before...On the otherhand if you run the SP more then once in a single session you can run into the same issue...

 

Ulrich

 

Ulrich