Defining Custom Stored Procedures

Jul 5, 2011 at 8:35 PM

I am unclear over the process.  What do I put in the SQL section.  The entire CREATE PROCEDURE Statement?  and then if it return a result set I then need to define each column it returns (including types) and I also need to separately define the parameters for the stored procedure?

 

Seems logical, although I am unclear if I put the entire CREATE in the SQL definition, how it will alter it later.

Can someone please provide a little guidance.  All I could find in the wiki is a sample function call.

 

Thanks,

Steve

Coordinator
Jul 8, 2011 at 3:13 AM

In the SQL statement you just put select statement. Do not add the create procedure or anything else. Think about it as a big select statement. Whatever field and parameters you use just add those the respective collections in the tree. A stored procedure will be generated in the background taking the defined parameters and returning the defined fields.

Jul 28, 2011 at 9:07 PM

So I tried this in version 4.1.0.199, everything verified and generated OK.

I then ran the DB upgrader, and tested.

An exception was thrown complaining that the stored procedure does not exist.  I checked to make sure it was not set to existing, and was set to Generate.

I do not know why the installer did not install the SP, and I cannot find an sql file in the project to match.

 

Steve

Aug 4, 2011 at 4:50 PM

Today I tried a different stored procedure with the latest version of nHydrate and the newer generator you provided.  Still no generation of actual stored procedure.

I have created a bug.

 

Steve

Coordinator
Aug 4, 2011 at 7:27 PM

Please verify that you did

1. Generate the database project with the new model
2. Verify that the SO is in the generated SQL files of the project
3. Ran the database project against the database

I just am ruling out the obvious.

Aug 4, 2011 at 7:36 PM

I understand.

1. CHECK

2. CANNOT FIND SP, checked everywhere in installer project.

3. RAN Installer

Steve

Coordinator
Aug 5, 2011 at 1:02 AM

You can check "show all files" and make sure the file is not on disk but just not in the project. this could happen if you generate while you are in run mode as files are not added to the project. Please verify that you do not see the SQL file in the folder "Stored Procedures\Generated\CustomStoredProcedures". you can also send me your model and I will determine what is wrong to chrisd@nhydrate.org.

Aug 5, 2011 at 2:57 PM

So it now generated, I found a the folder and a different sql file in the directory.  It must have been remnants of an old generation that I rolled back with my foundation server.  I made sure that that SP didn't belong anymore, deleted the folder and regenerated.  The SP is now in the project.  I ran the installer and at least now the SP is in the database!

The bug I created is now invalid.

Thanks,

Steve

Aug 5, 2011 at 3:25 PM

Side Note,

I wish I could put user defined functions in the model instead of maintaining a different create script for them

Steve

Coordinator
Aug 5, 2011 at 4:51 PM

You can add anything you wish to the upgrade and first install script. These are never over-generated and you can add whatever scripts you wish to them. The first install will be run only on a database that has never had the installer run on it. The generated upgrade scripts are run from the current database version to the new version in order, so you can add scripts here that should be run on upgrades.

Aug 5, 2011 at 4:56 PM

Good Info,

I will take advantage.  Great Work!

Steve