Stored Procedure Parameters

[DMT]

I have some parameters passed to my stored procedures in some cases that don't correspond to columns in the table (for example, a parameter passed to an Update procedure that indicates the stored procedure should turn off multi-user concurrency checking).

It looks like EA doesn't allow me to add any "columns" as stored procedure parameters that aren't columns in the database.

Is there any way to work around this limitation, and have EA allow me to add parameters to my stored procedure that are not in the table as columns?

[vprog]

I ran into the same problem a while back, and I've been monitoring the releases but haven't seen a fix for that yet.

I tried to work around it by not using the database profile but didn't have too much success with that.

[DMT]

Vprog:

Yeah, the database support isn't quite up to par with the rest of the product.  I'd like to see the following

- Support for non-column parameters in stored procedures
- Generation of stored procedures for SQL Server
- When generating tables, generation of DDL for SQL Server to create comments in the database. (Right now, the comments just go into the script itself--kind of useless unless they're in the database.)

We'll see what happens!  Perhaps the code generation templates will help out with some of this (although the support for non-column parameters would need to be fixed in the app itself.)

[DMT]

I should be more clear on what I mean by "Generation of stored procedures for SQL Server."

Today, EA will generate DDL to create the stored procedure, but it does not generate the parameters, at least as far as I can figure out.

[hd]

Improvements to database support (DDL import/export) will be rolled out from time to time. The next release should include generation of SQL Server table and column comments using the sp_addextendedproperty syntax, among other things.

Here's a workaround for generating non_parameter stored procedures...

1. Add a table(s) to a package, and select a target database.
2. Add an operation and give it the name of the stored procedure.
3. Select 'proc' from the stereotype combo.
4. Put the body of the procedure in the Notes section of the dialog. Save.
5. Right click on the package and select Code Engineering->Generate DDL.
6. Check 'Create Primary/Foreign Key Constraints' and 'Generate Stored Procedures'.
7. Generate the procedures.

We are developing generation of stored procedures with non-column parameters for a future release.

[DMT]

Yahoo!

Thanks for the update.