TVFImplementation Element

The TVFImplementation element implements the query view as a table-valued function. The element includes a required FunctionName Attribute and a child CreateFunctionSQL Element. Within the CreateFunctionSQL element, you provide a Transact-SQL CREATE FUNCTION script that the system uses to create a function in the database you load the query view spec.

Tip: The ViewImplementation element is also available to implement the query view as a database view. The main difference is that a database view is simpler and consists entirely of a SELECT statement, so you can't perform complicated logic with it or pass context or variables to it. If your query requires context or complicated logic, you need to use the TVFImplementation element. Otherwise, you generally should use the ViewImplementation element. List builders generally require context, so they typically use table-valued functions. But list builders that do not require context can use the ViewImplementation element to implement the query view as a database view.

<QueryViewSpec 
  xmlns="bb_appfx_queryview" 
  xmlns:common="bb_appfx_commontypes" 
  ID="AE91E1A7-2130-409C-8D06-198030EB0AA7" 
  Name="Food Bank TX List Builder Query CompositeColumn" 
  Description="Returns transactions for each food bank.       
  Used on the FoodBankWidgetDashboard.Page.xml.       
  Page name Food Banks with ID of 753229b2-9549-41ce-8058-027e9b5e9230" 
  Author="Technical Training" 
  IsRoot="false" 
  PrimaryKeyField="FOODBANKTXDETAILID" 
  RecordType="Food Bank Transaction Detail" 
  common:SecurityUIFolder="Constituent\Food Bank\Food Bank Transaction Header\Food Bank Transaction Detail" 
  AllowAttributeExtension="false" >
  <TVFImplementation FunctionName="USR_UFN_QUERY_FOODBANKTXDETAIL_LISTBUILDER2">
    <CreateFunctionSQL>

      create function dbo.USR_UFN_QUERY_FOODBANKTXDETAIL_LISTBUILDER2(@FOODBANKID uniqueidentifier = null)
      returns table
      as
      RETURN
      WITH CTE
      AS
      (
      SELECT
      USR_FOODBANK.CONSTITUENTID AS FBCONSTITUENTID
      , USR_FOODBANKTXHEADER.ID as FOODBANKTXHEADERID
      , USR_FOODBANKTXDETAIL.ID as FOODBANKTXDETAILID
      , CONSTITUENT.NAME AS FBCONSTITUENTNAME
      , USR_FOODBANKTYPECODE.DESCRIPTION AS FOODBANKTYPE
      , USR_FOODBANKTXHEADER.CONSTITUENTID AS TXCONSTITUENTID
      , CONSTITUENT_1.NAME AS TXCONSTITUENTNAME
      , USR_FOODBANKTXHEADER.FOODBANKTXTYPE
      , USR_FOODBANKTXHEADER.TXDATE
      , USR_FOODBANKTXHEADER.RECEIPTPRINTED
      , USR_FOODBANKTXHEADER.DATEADDED AS TXDATEADDED
      , USR_FOODBANKTXHEADER.DATECHANGED AS TXDATECHANGED
      , USR_FOODITEM.NAME as FOODITEMNAME
      , USR_FOODBANKTXDETAIL.FOODITEMAMOUNT
      , USR_FOODBANKTXDETAIL.LINEAMOUNT
      , USR_FOODBANKTXDETAIL.QUANTITY
      , USR_FOODITEM.CURRENTCOST
      , USR_FOODITEM.LOWINVENTORYTHRESHOLD
      , USR_FOODITEM.WEIGHT

      ,TWITTERUSERID  = ISNULL((SELECT
      [USERID]
      FROM [SOCIALMEDIAACCOUNT]
      INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
      --INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
      WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID =  USR_FOODBANK.CONSTITUENTID
      AND SOCIALMEDIASERVICE.NAME = 'Twitter'),'')

      , TWITTERURL = ISNULL((SELECT [URL]
      FROM [SOCIALMEDIAACCOUNT]
      INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
      --  INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
      WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
      AND SOCIALMEDIASERVICE.NAME = 'Twitter'),'')

      , FACEBOOKUSERID = 	ISNULL((SELECT
      [USERID]
      FROM [BBInfinity].[dbo].[SOCIALMEDIAACCOUNT]
      INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
      --INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
      WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID =  USR_FOODBANK.CONSTITUENTID
      AND SOCIALMEDIASERVICE.NAME = 'Facebook'),'')

      , FACEBOOKURL = ISNULL((SELECT [URL]
      FROM [BBInfinity].[dbo].[SOCIALMEDIAACCOUNT]
      INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
      --INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
      WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
      AND SOCIALMEDIASERVICE.NAME = 'Facebook'),'')

      FROM         USR_FOODBANK INNER JOIN
      USR_FOODBANKTXHEADER ON USR_FOODBANK.ID = USR_FOODBANKTXHEADER.FOODBANKID INNER JOIN
      USR_FOODBANKTXDETAIL ON USR_FOODBANKTXHEADER.ID = USR_FOODBANKTXDETAIL.FOODBANKTXHEADERID INNER JOIN
      USR_FOODBANKTYPECODE ON USR_FOODBANK.FOODBANKTYPECODEID = USR_FOODBANKTYPECODE.ID INNER JOIN
      USR_FOODITEM ON USR_FOODBANKTXDETAIL.FOODITEMID = USR_FOODITEM.ID INNER JOIN
      CONSTITUENT ON USR_FOODBANK.CONSTITUENTID = CONSTITUENT.ID INNER JOIN
      CONSTITUENT AS CONSTITUENT_1 ON USR_FOODBANKTXHEADER.CONSTITUENTID = CONSTITUENT_1.ID
      WHERE (@FOODBANKID is null
      or USR_FOODBANK.CONSTITUENTID = @FOODBANKID)
      )

      SELECT
      FBCONSTITUENTID
      , FOODBANKTXHEADERID
      , FOODBANKTXDETAILID
      , FBCONSTITUENTNAME
      , FOODBANKTYPE
      , TXCONSTITUENTID
      , TXCONSTITUENTNAME
      , FOODBANKTXTYPE
      , TXDATE
      , RECEIPTPRINTED
      , TXDATEADDED
      , TXDATECHANGED
      , FOODITEMNAME
      , FOODITEMAMOUNT
      , LINEAMOUNT
      , QUANTITY
      , CURRENTCOST
      , LOWINVENTORYTHRESHOLD
      , WEIGHT

      ,TWITTERUSERID as SOCIAL_TWITTERUSERID
      , TWITTERURL as SOCIAL_TWITTERURL
      , 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.twitter16.png' 
      as SOCIAL_TWITTERIMAGEKEY

      , FACEBOOKUSERID as SOCIAL_FACEBOOKUSERID
      , FACEBOOKURL	as SOCIAL_FACEBOOKURL
      , 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.facebook.png'  
      as SOCIAL_FACEBOOKIMAGEKEY

      , 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.twitter16.png' 
      as TESTIMAGEKEY
      
      FROM CTE
    </CreateFunctionSQL>
    <Parameters>
      <common:FormMetaData>
        <common:FormFields>
          <common:FormField FieldID="FOODBANKID" Caption="Food Bank" Required="false" DataType="Guid">
            <common:SearchList SearchListID="3eceea4c-cf42-4938-9cf1-c7c77ebc7dc9" EnableQuickFind="true" />
          </common:FormField>
        </common:FormFields>
      </common:FormMetaData>
    </Parameters>
  </TVFImplementation>
  <Output>
    <OutputFields>
      <OutputField Name="FBCONSTITUENTID" IsHidden="true" DataType="Guid" />  ...