USP_DATAFORMTEMPLATE_VIEW_EDUCATIONALINSTITUTIONDETAILS

The load procedure used by the view dataform template "Educational institution details view"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@CLASSIFICATIONCODE	nvarchar(100)	INOUT	Classification
@TYPECODE	nvarchar(100)	INOUT	Type
@FICECODE	nvarchar(100)	INOUT	FICE code
@LOCATIONCODE	nvarchar(100)	INOUT	Location
@INFOSOURCECODE	nvarchar(100)	INOUT	Information source
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Website
@EDUCATIONALINSTITUTIONID	uniqueidentifier	INOUT	EDUCATIONALINSTITUTIONID

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EDUCATIONALINSTITUTIONDETAILS
        (
            @ID uniqueidentifier,
            @DATALOADED bit = 0 output,
            @NAME nvarchar(100) = null output,
            @CLASSIFICATIONCODE nvarchar(100) = null output,
            @TYPECODE nvarchar(100) = null output,
            @FICECODE nvarchar(100) = null output,
            @LOCATIONCODE nvarchar(100) = null output,
            @INFOSOURCECODE nvarchar(100) = null output,
            @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
          @EDUCATIONALINSTITUTIONID uniqueidentifier = null output
        )
        as
            set nocount on;

            -- be sure to set this, in case the select returns no rows

            set @DATALOADED = 0;

          set @EDUCATIONALINSTITUTIONID = @ID;

            -- populate the output parameters, which correspond to fields on the form.  Note that

            -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

            -- will display a "no data loaded" message.

            select @DATALOADED = 1,
                 @NAME = dbo.EDUCATIONALINSTITUTION.NAME,
                 @CLASSIFICATIONCODE =case dbo.EDUCATIONALINSTITUTION.CLASSIFICATIONCODE when 0 then 'School' when 1 then 'College' when 2 then 'Other' else '' end,
                 @TYPECODE = dbo.EDUCATIONALHISTORYTYPECODE.DESCRIPTION,
                 @FICECODE=dbo.EDUCATIONALINSTITUTION.FICECODE,
                 @LOCATIONCODE=dbo.EDUCATIONALINSTITUTIONLOCATIONCODE.DESCRIPTION,
                 @INFOSOURCECODE=dbo.INFOSOURCECODE.DESCRIPTION,
                 @WEBADDRESS = dbo.CONSTITUENT.WEBADDRESS                 
            from dbo.EDUCATIONALINSTITUTION
                left outer join dbo.CONSTITUENT on dbo.EDUCATIONALINSTITUTION.ID = dbo.CONSTITUENT.ID
                left outer join dbo.EDUCATIONALHISTORYTYPECODE on EDUCATIONALINSTITUTION.TYPECODEID = EDUCATIONALHISTORYTYPECODE.ID
                left outer join dbo.EDUCATIONALINSTITUTIONLOCATIONCODE on EDUCATIONALINSTITUTION.LOCATIONCODEID = EDUCATIONALINSTITUTIONLOCATIONCODE.ID
                left outer join dbo.INFOSOURCECODE on EDUCATIONALINSTITUTION.INFOSOURCECODEID=INFOSOURCECODE.ID
            where dbo.EDUCATIONALINSTITUTION.ID = @ID

            return 0;