We recently converted many of our static stored procedures to dynamic sql to reduce the amount of duplicated code. Does Teradata have a function or process that would allow the users to view the SQL statements that would be generated by passing in different parameters? I've heard SQL Server has a similar function that would allow you to call the stored procedure and display the SQL statement without executing.
Any help at all on this would be greatly appreciated!
You can use DYNAMIC RESULT SETS .
Then you have to create a cursor using then string variable where you storage the dynamic sql statement, for instance:
DECLARE cursor_1 CURSOR WITH RETURN FOR SELECT :sql_stmt ;
Finally you should open that cursor to have a look the value inside :sql_stmt