Retrieve the TimeZoneEntry

Previous: Add a Custom Table

The Google Time Zone API will return a time zone name as part of the result of the call for a given set of coordinates. The name of the time zone returned by the API matches the values found within the TIMEZONEENTRY.NAME column. Our custom USR_ADDRESSCOORDINATETIMEZONE table requires a foreign key value to the TIMEZONEENTRY table. So we need a mechanism to retrieve the GUID of the TIMEZONEENTRY.ID column for a time zone name. Let's create a stored procedure to retrieve the GUID for a given time zone name. We will utilize a SQLStoredProcedureSpec to hold the definition of our stored procedure so that we can gracefully deploy and install the stored procedure using the Catalog Browser user interface within the Blackbaud CRM user interface.

Step 1 - Add a SQLStoredProcedureSpec to your catalog project

In Visual Studio, right-click the catalog project within the Solution Explorer and select Add\New Item... from the context menu (Ctrl + Shift + A).

On the left side of the dialog window, select Blackbaud AppFx Catalog from the list of Installed Templates.

Select the SQL Stored Procedure Spec catalog item and give the spec a name. I named the spec file: USR_USP_TIMEZONEENTRY_GETBYNAME.xml.

Figure: Add the SP spec

Step 2 - Load the SQLStoredProcedureSpec

Review the completed SQLStoredProcedureSpec below and load into the database via LoadSpec.

Completed SQLStoredProcedureSpec

<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="7ec5c329-4e4a-46f0-ba50-5090edddd77a"
	Name="USR_USP_TIMEZONEENTRY_GETBYNAME"
	Description="Retrieve the TimeZoneEntry.ID for the given Name.  
  Used by Address Coordinate TimeZone Global Change (Custom)"
	Author="Technical Training"
	SPName="USR_USP_TIMEZONEENTRY_GETBYNAME"
	>
	<CreateProcedureSQL>

<![CDATA[

create procedure dbo.USR_USP_TIMEZONEENTRY_GETBYNAME
(
@TimeZoneName as varchar(400)
)
as
DECLARE @RETVAL as uniqueidentifier

begin
	Select @RETVAL = ID 
  from TIMEZONEENTRY
  WHERE NAME = @TimeZoneName
  
  SELECT @RETVAL
  
end

]]>

</CreateProcedureSQL>

</SQLStoredProcedureSpec>

Next: Code the CLR Class