SQL Anywhere database - select into #temp

Author

select into #temp - results problem

Rebecca Snyder

2005-08-23, 8:23 pm

SQL Anywhere 9.0.2.2551
I've read all over the forums looking for an answer to this, but although I
see a lot on this topic, I don't see my solution so bear with me.
I'm creating a dynamic temporary table in a stored procedure. The following
sql returns a good result set when I cut and paste and plug in the values.
But when I call the stored procedure from my program, I'm getting
'#TempTypeTable' not found. Any ideas? TIA!!

ALTER PROCEDURE "DBA"."sp_dynamicselect"( IN TableName CHAR(128),
Col1 Char(80),
Col2 Char(80),
Col3 Char(80))
RESULT( "TypeCode" INT, "TypeShort" Char(15), "TypeDesc" Char(200))
BEGIN
DECLARE Command LONG VARCHAR;
SET Command = 'Select Cast(' || Col1 || ' as Integer) as TypeCode,
cast(' || Col2 || ' as char(15)) as TypeShort,
cast(' || Col3 || ' as char(200)) as TypeDesc Into
#TempTypeTab1 From dba.' || TableName;
Execute Immediate Command;
Select * from #TempTypeTable;
END

My "call" is:

CALL dba. sp_dynamicselect('Ve
ndors', 'VendorNumber', 'IRTUnit',
'VendorName')

Becky Snyder
Bradley University
becky@bradley.edu

Bruce Hay

2005-08-23, 8:23 pm

Try:

ALTER PROCEDURE "DBA"."sp_dynamicselect"( IN TableName CHAR(128),
Col1 Char(80),
Col2 Char(80),
Col3 Char(80))
RESULT( "TypeCode" INT, "TypeShort" Char(15), "TypeDesc" Char(200))
BEGIN
DECLARE Command LONG VARCHAR;
SET Command = 'Select Cast("' || Col1 || '" as Integer) as TypeCode,
cast("' || Col2 || '" as char(15)) as TypeShort,
cast("' || Col3 || '" as char(200)) as TypeDesc
From DBA."' || TableName || '"';
Execute Immediate Command with result set on;
END

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Rebecca Snyder" <becky@bradley.edu> wrote in message
news:430b776f$1@foru
ms-2-dub...
> SQL Anywhere 9.0.2.2551
> I've read all over the forums looking for an answer to this, but although
I
> see a lot on this topic, I don't see my solution so bear with me.
> I'm creating a dynamic temporary table in a stored procedure. The
following
> sql returns a good result set when I cut and paste and plug in the values.
> But when I call the stored procedure from my program, I'm getting
> '#TempTypeTable' not found. Any ideas? TIA!!
>
> ALTER PROCEDURE "DBA"."sp_dynamicselect"( IN TableName CHAR(128),
> Col1 Char(80),
> Col2 Char(80),
> Col3 Char(80))
> RESULT( "TypeCode" INT, "TypeShort" Char(15), "TypeDesc" Char(200))
> BEGIN
> DECLARE Command LONG VARCHAR;
> SET Command = 'Select Cast(' || Col1 || ' as Integer) as TypeCode,
> cast(' || Col2 || ' as char(15)) as TypeShort,
> cast(' || Col3 || ' as char(200)) as TypeDesc
Into
> #TempTypeTab1 From dba.' || TableName;
> Execute Immediate Command;
> Select * from #TempTypeTable;
> END
>
>
> My "call" is:
>
> CALL dba. sp_dynamicselect('Ve
ndors', 'VendorNumber', 'IRTUnit',
> 'VendorName')
>
>
> Becky Snyder
> Bradley University
> becky@bradley.edu
>
>

Rebecca Snyder

2005-08-23, 8:23 pm

That did it! ... with a slight rearranging of the statement:

Execute Immediate WITH RESULT SET ON Command;

Thanks so much for the quick response!
Becky

"Bruce Hay" < h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m> wrote in message
news:430b7d12$1@foru
ms-2-dub...
> Try:
>
> ALTER PROCEDURE "DBA"."sp_dynamicselect"( IN TableName CHAR(128),
> Col1 Char(80),
> Col2 Char(80),
> Col3 Char(80))
> RESULT( "TypeCode" INT, "TypeShort" Char(15), "TypeDesc" Char(200))
> BEGIN
> DECLARE Command LONG VARCHAR;
> SET Command = 'Select Cast("' || Col1 || '" as Integer) as TypeCode,
> cast("' || Col2 || '" as char(15)) as TypeShort,
> cast("' || Col3 || '" as char(200)) as TypeDesc
> From DBA."' || TableName || '"';
> Execute Immediate Command with result set on;
> END
>
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "Rebecca Snyder" <becky@bradley.edu> wrote in message
> news:430b776f$1@foru
ms-2-dub...
> I
> following
> Into
>
>

Sponsored Links