PREPARE Sql in Stored Procedure

Database
Enthusiast

PREPARE Sql in Stored Procedure

Hi all,
I want to pass a sql string to a StoredProcedure,
then use a cursor in the SP from that string.
According to the docs, I should be able
to use a 'DECLARE cur1 CURSOR :statement'
if the statement was previously prepared.
CanI do that in the SP from the variable?

I'm trying:
REPLACE PROCEDURE pOne(IN strquery VARCHAR(200))
BEGIN
PREPARE pStmt FROM :strquery;
DECLARE cur1 CURSOR FOR :pStmt;
END

I get the following errors:
SPL1027:E(L3), Missing/Invalid SQL statement'E(3706):Syntax error: expected something between the 'BEGIN' keyword and the 'PREPARE' keyword
SPL1027:E(L4), Missing/Invalid SQL statement'E(3707):Syntax error: expected something between the 'END' keyword and the 'DECLARE' keyword

Any suggestions please?
5 REPLIES
Senior Apprentice

Re: PREPARE Sql in Stored Procedure

"According to the docs" you can do that only in Embedded SQL.

What are you trying to achieve by passing a string to a cursor?
Kind of dynamic SQL?
A "one size fits all" SP?

How are you going to access the selected columns without knowing the names?

Any security could be bypassed by that:
select any_column from a_table_i_dont_have_select_rights_on_but_the_SPs_owner;

Dieter
Enthusiast

Re: PREPARE Sql in Stored Procedure

Thanks for the reply Dieter,

Yes, I see I'm getting confused between Embedded SQL and Dynamic SQL in SPs.
Good Point about the security too.

I'm trying to port an existing app that generates dynamic sql on the
server and currently calls a SP in MySql that loads the results
of the query that's passed into a temp table in memory. That table is
then joined on to limit the results of the query we ultimately
want the resultset for.

To get it done quickly, I was seeing if I could pull of something similar in TD.
I just need the results of the query loaded into some construct
I can use as a constraint for another query that's dynamically generated
in the SP. The cursor is just a set of records from a list of ids,
but when I do the select I really want, I want to constrain by those ids.
The query that's generated always uses a particular key, and
that's all I should need to be able to do what I need to do.

I've found another way to do it for now but I really appreciate your
input and taking time to reply.

Regards,
Pete
Senior Apprentice

Re: PREPARE Sql in Stored Procedure

> I'm trying to port an existing app that generates dynamic sql on the
> server and currently calls a SP in MySql that loads the results
> of the query that's passed into a temp table in memory.

In Teradata this would be a
CALL dbc.sysexecsql('create volatile table xxx as select ... with data primary index (..);');

> That table is
> then joined on to limit the results of the query we ultimately
> want the resultset for.

You simply join to that volatile table.

> I just need the results of the query loaded into some construct
> I can use as a constraint for another query that's dynamically generated
> in the SP. The cursor is just a set of records from a list of ids,
> but when I do the select I really want, I want to constrain by those ids.

If you dynamically generate the second query, too, why don't you create a single query which joins to the first query as a Derived Table?

If it's known list of columns to be returned as result set from a SP in Teradata, Global Temporary tables are used. The GT table is created once, within the SP there's a delete and insert/select and after the call you can select from that GT.
Btw, SQL Server SPs often might be replaced by a Teradata macro, which can return result sets.

> I've found another way to do it for now but I really appreciate your
> input and taking time to reply.

Could you share that "other way"?

Dieter
Enthusiast

Re: PREPARE Sql in Stored Procedure

Hi Dieter,

I was going to use a volatile table,
but my understanding was that if multiple users hit the SP,
only one instance of the volatile table can exist with the same name
in the db. So one caller could potentially error
or whack another users results. Is this true?
If so, I'm not sure if I manage by associating results in
the volatile table with a userid( or sequence), or by giving each user
a volatile table that uses a naming convention that includes
their id so I can drop. Same with the global temp table.

And, OK, there's a little more to this whole thing,
the original SP (MySql) is called with the following args(all VARCHARs)
(prequery, postquery, subquery, docidlist)

where: prequery and postquery are parts of a query manipulated
in the SP to generate the query that joins with subquery and docidlist.

subquery is the query I want to use as a derived table, but
I'm also handed docidlist, a comma separated list of docids.

In MySql I just issue the subquery and load the results (ids) into a TEMP table in memory.
I run a split_string SP on the docid list and load those ids into a TEMP table in memory.
I create and issue the query for prequery and postquery joining with those TEMP tables
and return the results from the SP.

In TD, I'm not using a SP as described above at the moment.
(Along with "other way" I should've stressed "for now" as
I've got something working but I'm still trying to figure out
the optimal way to do this.)

I've added 2 static tables : subsetids, searchids
Instead of calling the SP, I issue the subquery to populate subsetids
(with an added userid column, which is just a sequence),
I then call a split_string SP to insert the searchids (with same userid),
I then create the query I need joining those tables to get the results.
I then delete from the tables for that id.

This is all done from the app, the only SP is a call to split_string,
maybe not needed at this point, but I was thinking may be helpful
if I get everything going in the main SP I'm trying to create.
The whole thing is not optimal, and it pains me to admit it,
but that's what I'm doing at the moment.
Plus, all inserts through jdbc in teradata are tera-hurting.
I'm trying to figure out how to do this using derived tables, a cursor, or
something in memory so I can avoid the inserts, but I'll probably have to
refactor the app to do this. That's not an option for me at the moment,
so I'm researching now for when it is (I've been told next week).

Thanks Again,
Pete

Senior Apprentice

Re: PREPARE Sql in Stored Procedure

Hi Pete,

> I was going to use a volatile table,
> but my understanding was that if multiple users hit the SP,
> only one instance of the volatile table can exist with the same name
> in the db. So one caller could potentially error
> or whack another users results. Is this true?

No, a Volatile Table is always session local.
You may have several session (even with the same user) with the same name, but diffenrent data/definition.
The create will only fail if a real table with that name already exists.

So you can savely use it within a SP.

> Same with the global temp table.

CREATE GLOBAL TEMPORARY TABLE just defines a kind of template within the data
dictionary. Each user with granted access rights can materialize a version
within the current session, similar to a VT.

So you can savely use it within a SP, too.

> subquery is the query I want to use as a derived table, but
> I'm also handed docidlist, a comma separated list of docids.

Ouch, this is really "dynamic"

> In MySql I just issue the subquery and load the results (ids) into a TEMP table in memory.
> I run a split_string SP on the docid list and load those ids into a TEMP table in memory.
> I create and issue the query for prequery and postquery joining with those TEMP tables
> and return the results from the SP.

An SP can't return an answer set in Teradata (at least in the current releases), that's why you insert/select into a Global Temp table and select from that afterwards.
But IMHO you don't have to use a temp table for "subquery":
No matter if it's a "select foo from bar" or a "12,345,678,890", just put into brackets and incorporate it into your pre/postquery text.
call dbc.sysexecsql('select ...... from ....where id in (subquery);"

> This is all done from the app

So why don't you create the select within your app?
And then just submit it, so you don't have to deal with temp tables.

> Plus, all inserts through jdbc in teradata are tera-hurting.

You have to code it as Multistatements or using JDBC's addBatch/executeBatch, this will speed it up.

Btw, "not optimal" is the right description, you probably used mysql for too long :-)

Dieter