Toad World® Forums

Executing Stored Procedure in Workbook

Running TDP workbook and trying to figure out how to use a stored procedure in my query. Tried the following: 1) adding stored procedure to diagram; 2) manually entering EXECUTE stored procedure in query tab; and 3) copying stored procedure code into query tab. In the first case, I'm getting an error "the object UNEARNED_ROLLFORWARD cannot be added to the Query Builder. STOREDPROC type objects cannot be modeled". In the other two cases I'm able to execute the query and gets results, but get error when a try to visualize the SQL statement or save the workbook.

You mean you need to use a function i your SQL, correct? Only a function will have a return value. You would add a calculated field that called the function with appropriate input variables.

No, I’m trying to figure out if I can execute a stored procedure in TDP, get the results, and then add pivot and report steps on the results.

Try using the SQL Editor

In SQL Editor, I was able to execute the stored procedure using bind variables and get results. Used the Excel export icon to export results. Was able to create a single pivot (but not multiple). Saved the workbook as a .twf but it didn't save the SQL Editor. Saved the SQL Editor separately as a .tef file.

There's no way to execute the stored procedure using Query Builder correct? Our end users are primarily using workbook so I'm trying not to confuse them with other interfaces as much as possible. Plus if we need multiple pivots and reports we will need to use workbook.

You need to use the SQL Editor. But you should be able to and multiple pivots and the tef should be saved with the workbook. When you started the workbook did you chose this option?

I didn’t use that option, I will give it a try. Thanks.

I tried using Query Editor and am able to create multiple pivots and save them in the workbook—thanks.