Using a MS SQL Server Procedure to Populate Project Properties

If you read my blog about Cascading Attributes and found that useful, here’s another way to do something similar, but a bit more complicated.

Recently I was asked, “How do I create a SELECT statement to do this?”

I have two Project Properties:
          @"PROJECT_Project_Number" and @"PROJECT_Billing_Type"

Here is the logic I want to use:
          If @"PROJECT_Project_Number" is a NULL then
                  @"PROJECT_Billing_Type" = Non-billable (pre-contract)
          Else If the first character of @"PROJECT_Project_Number" is a 'P' then
                  @"PROJECT_Billing_Type" = Non-billable (pre-W)
                  Else If the first character of @"PROJECT_Project_Number" is a 'W'
                   @"PROJECT_Billing_Type" = Non-billable (pre-contract)
                   @"PROJECT_Billing_Type" = Billable (Contracted)
          End If

Well, I’m no SQL guru, but I do know that it would be tough to do this with just plain SQL, but pretty easy if you create and use a stored SQL Server procedure instead! (And of course, you can do something similar with Oracle.)

First setup the Billing_Type Project Property
Using the ProjectWise Administrator Client, create the necessary Project Type and add your Project Properties as usual, but you will need to provide a default value for Billing_Type as well as adding a “select” statement that actually calls a stored procedure. Here’s an example Project Type (Billing Example) with some Project Properties.

Take a good look at “Project_Number” and “Billing_Type”.


Now create the stored procedure in SQL Server
I used the tools inside of Microsoft SQL Server Management Studio, but no matter what tool you use, you want a stored procedure that looks like this:

Now test the stored procedure
Create a new Project in ProjectWise Explore and then go to the Project Properties page.  Your “Billing_Type” should update based on what you enter for the “Project_Number”.  Here’s how it should look like with some sample data.