Executing SQL from within a Stored Procedure

[Liam1]

Hello,

I have a stored procedure and want to execute some derived SQL.

I have tried

CALL DBC.SYSEXECSQL ('Select ' || :Measure_Calc || ';');

But do not have access

I have also tried

EXEC SQL 'Select ' || :Measure_Calc || ';'
END EXEC;

The second one should work, according to the manual. I keep getting the error mesages saying that Teradata is not expecting the EXEC or the SQL bits.

Does anyone else know how to do this?

Thanks in advance

Liam

 

[dnoeth]

What do you mean by "But do not have access"?
Is there an error message?

"call dbc.sysexecsql ('sql command')" is supposed to work (only within a SP).

"EXEC SQL" is Embedded SQL only and can't be used within a SP.

Dieter

 

[Liam1]

DNoeth,

I am trying to compile a stored procedure.

e.g.

Replace My_Proc
( Out My_Var
)
Begin
DECLARE

CALL DBC.SYSEXECSQL ('Select Current_time;')
;

End;

I get the error message:
SPL1078:E(L227), User is not authorized to use the DDL/DCL/Dynamic SQL statement.

If I try to do a Show Procedure DBC.SYSEXECSQL, i get the message it does not exist - these errors do seem to conflict with each other - i.e. how can it way I am not authorised, if it does not exist?

Is this just a "feature" of Teradata V2R5?

Thanks,

Liam

 

[dnoeth]

Did you check the error message in the Messages manual:
"Explanation: The reason is that the creator and the
immediate owner of the stored procedure is not the
same. Hence the specified DDL, DCL, or dynamic SQL
statement cannot be specified in the SPL source text."

To be able to use Dynamic SQL the creator must the owner, too.
I.e. you must create it within your logged in user.

The dbc.sysexecsql doesn't exist, it's only "visible" within a SP, so it may be used only in a SP.

Read the SP manual:
"Rules for Using SQL Statements in Stored Procedures"
"Using Dynamic SQL in Stored Procedures"

Dieter, on the way to two weeks of vacation in Greece ;-)

 

[Liam1]

dnoeth,

Good point about reading the manual...

Basically what I am trying to do is:

if you use query man and type
Select 1+1; you get 2

In a stored procedure, if i try to say
Select :MyVar1 into MyVar2 i get '1+1'

Is there a way to get the actual calculated answer as the variable with out using the dbc.sysexecsql?

I am trying to convert a procedure from SQL Server to Teradata...

Thanks again,

Liam.

 

[CapsuleCorpJX]

I think its not possible to do a dynamic select statement from a stored procedure:

http://www.teradata.com/t/go.aspx/index.html?id=117793

Which really sucks, dynamic selects are the most useful dyn sql statements to have.

There is a work around for it at the end of that website, but it seems too much trouble to me.

 

[Liam1]

CapsuleCorpJX,

I need to get access to the DBC.SysExecSQL stored procedure. I am on the case!

Thanks,

Liam.

 

[tdatgod]

Hi,
There are no permissions required to use the

DBC.SysExecSQL

Store procedure call. The only requirement is that if your LOGON is

John

you are in

database John ;

if you logon as

John

and your set up by default takes you to

database Finance;

you can't write store procedures using the above interface.

This is a security issue.

Therefore you must see if your logon ID has any PERM space associated with it.

sel databasename,permspace
from dbc.databasesx;

will show you all the databases you have access to.


As a side note

DYNAMIC SELECT is NOT ALLOWED because it would open a SECURITY HOLE you could drive a truck through if it was allowed.

This is why Dynamic SELECT is NOT ALLOWED.

 

[CapsuleCorpJX]

Interesting. Is there an document explaining it?
I heard MS SQL Server allows dynamic selects, I'm not sure if its secure or not though.

Anyway, I wonder why they can't just automate it for us. We can do dynamic sql selects, we just have to go through the trouble of using INSERT-SELECT into a temp table.

 

[tdatgod]

Hi,
I don't believe SELECT in any form including INSERT-SELECT is allowed from DYNAMIC SQL, but if you have tried this and it works I guess I am wrong. Either that or it is a bug and that functionality may go away when it is fixed. Again ANY dynamic select might allow a user to access a table which he doesn't have access to because of when access rights are checked.

The Access Rights usage of Stored procedure are explained in the SQL Reference manual about Stored procedures.

 

[tdatgod]

Hi,
Well given the other post I strand corrected. I will have to verify why this works and make sure it isn't a BUG.

 

[tdatgod]

Well I stand corrected. from the developer.

You can’t return an answer set to stored procedures. The only select you can issue in an SP is the select into that returns a scalar result (one result not a set). You can also issue a select as part of a cursor operation and then read the results one row at a time with a cursor. In the case of a dynamic SQL statement it does not have a result parameter, because there is no way to specify a result, therefore you can’t use a select statement. An insert/select inserts the results into a table which can then be read via a cursor operation. Nothing special about access rights.


So please ignore all my previous posts and follow the instructions from

http://www.teradata.com/t/go.aspx/index.html?id=117793

To support DYNAMIC SQL with SELECT in it.