Creating Stored Procedures in Batch

Database
Enthusiast

Creating Stored Procedures in Batch

Hi,

I need to create multiple stored procedures in a single script, i mean

I am having definitions of 5 stored procedures.Individually they get created but when same definitions are executed in batch then the following Error Occurs

"Only an ET or null statement is legal after a DDL Statement. "

If anyone knows the solution please let me know..

Thanks
9 REPLIES
Teradata Employee

Re: Creating Stored Procedures in Batch

Hello,

If you are using BTEQ script for batch, you can use ".COMPILE FILE sp1.sql" to compile the SP.

Regards,

Adeel
Enthusiast

Re: Creating Stored Procedures in Batch

Thanks for Reply,

I am using Teradata SQL Assistant for creation of the stored procedures.They get created if executing individually but i need to execute all in a batch.

Thanks
Teradata Employee

Re: Creating Stored Procedures in Batch

For my information, how can you write a batch in SQL Assistant?

And can't you write a BTEQ script for a batch task?

Regards,

Adeel
Enthusiast

Re: Creating Stored Procedures in Batch

Hi ,
IF I am executing following three statements at a time then they get executed even if executed individually then they also get executed as like same I need to create 5 stored procedure

CREATE TABLE ALFCT_STATICCHANNEL(
STATICCHANNELID Decimal(18,0) NOT NULL,
STATICCHANNELTYPE varchar(256) NULL)
PRIMARY INDEX ( STATICCHANNELID );

CREATE TABLE ALENT_ARTWORKCAMPAIGN(
CAMPAIGNID Decimal(18,0) NOT NULL,
CAMPAIGNNAME varchar(255) NULL,
VALIDTO Timestamp NULL,
CAMPAIGNTYPE varchar(100) NULL)
PRIMARY INDEX ( CAMPAIGNID );

CREATE TABLE ALFCT_STOCK(
STOCKID Decimal(18,0) NOT NULL,
STOCKLEVEL Decimal(18,0) NULL,
MINORDERLEVEL Decimal(18,0) NULL)
PRIMARY INDEX ( STOCKID );

For my information will you please tell me how to create/Execute the batch in BTEQ.
Teradata Employee

Re: Creating Stored Procedures in Batch

It should work, this way in SQL Assistant as well, e.g I just tried following on V12:

create procedure sp1()
begin
...
end;

create procedure sp2()
begin
...
end;

create procedure sp3()
begin
...
end;

In case of BTEQ scripts, suppose you are working in a folder "C:\SPTest" and you have saved all definitions (starting from "Create" till "End;") of all SPs in seperate files (e.g. sp1.spl, sp2.spl, sp3.spl) in the same folder, following BTEQ script will compile all three in batch mode:

.LOGON server ip/login,pass;

.DATABASE database;

.COMPILE FILE = sp1.spl;
.COMPILE FILE = sp2.spl;
.COMPILE FILE = sp3.spl;

.LOGOFF;

HTH.

Regards,

Adeel
Enthusiast

Re: Creating Stored Procedures in Batch

Thanks,

I am using Teradata SQL Assistant 7.1

Re: Creating Stored Procedures in Batch

Is there any easy way to convert SQL Server stored procedures to Teradata stored procedures? Has anyone used utility like SwisSQL to do something like that?

Any other tips to migrate from SQL server to Teradata where a lot of logic is in stored procedures.

Re: Creating Stored Procedures in Batch

Ive faced the same problem.

I have a stored procedure to run a sequence of DDL statements, this apparently is not allowed unless the DLL is completed with ET.

However if your procedure is called from with in a transaction, the above would not work either. Since you have a open transaction.

Below is an example of DDL in my SP.

CREATE PROC .....
BEGIN

CREATE TABLE.....
IF (some condition)
ALTER TABLE....
END IF

CREATE STATS...

END

The above is not syntactically correct.

How do i get this to work in manual commit mode in Sql Assistant.
Junior Contributor

Re: Creating Stored Procedures in Batch

"I have a stored procedure to run a sequence of DDL statements, this apparently is not allowed unless the DLL is completed with ET."

Any DDL statement must be immediately commited in Teradata, a Stored Procedure simply follows that rule.

"However if your procedure is called from with in a transaction, the above would not work either. Since you have a open transaction."

Of course an ET would work, but it commits the transaction.

"How do i get this to work in manual commit mode in Sql Assistant."

Manual commit mode is for ANSI sessions, but you talk about ET, which is only allowed for BTET sessions?

Dieter