USP_SMARTQUERY_CREATEORUPDATE_TVF

CREATE procedure [dbo].[USP_SMARTQUERY_CREATEORUPDATE_TVF]
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50),
    @TVFNAME nvarchar(128),
    @PRIMARYKEYFIELD nvarchar(128),
    @TVFPARAMETERLIST nvarchar(1000),
    @FILTERDEFXML xml,
    @OUTPUTDEFXML xml,
    @SMARTQUERYSPECXML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @HASAPPUSERIDPARAM bit =0,
    @SPECUINAME nvarchar(60) = N'',
    @MARTKEY nvarchar(255) = null,
    @TVFSCHEMA nvarchar(255) = null

as

--this routine registers a TVF-based smart query in the catalog


set nocount on;

begin try

    declare @RECORDTYPEID uniqueidentifier;
    declare @OBJID int;
    declare @EXISTINGID uniqueidentifier;

    if @SPECUINAME is null
        set @SPECUINAME = N'';

    if @ID is null
        begin
        select @EXISTINGID = ID from dbo.SMARTQUERYCATALOG where NAME = @NAME;
        set @ID = @EXISTINGID;
        end
    else
        select @EXISTINGID = ID from dbo.SMARTQUERYCATALOG where ID = @ID;

    if @CHANGEAGENTID is null  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @MARTKEY is null

    begin

        select @OBJID = id from dbo.sysobjects where name = @TVFNAME and type in (N'FN', N'IF', N'TF', N'FS', N'FT');
        if @OBJID is null
            begin
                raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @TVFNAME);
                return 1;
            end

        declare @PARAMNAME nvarchar(128);
        select @PARAMNAME = PARAMETER_NAME from INFORMATION_SCHEMA.PARAMETERS 
            where specific_schema = 'dbo' and specific_name = @TVFNAME and IS_RESULT = 'NO' and parameter_name = '@MAXROWS' and data_type = 'int';

        if @PARAMNAME is null
            begin
                raiserror ('Table-valued function ''%s'' must have a @MAXROWS as int parameter defined.', 16, 1, @TVFNAME);
                return 2;
            end

    end

    declare @SECURITYUIFOLDER nvarchar(255);
    set @SECURITYUIFOLDER=
        coalesce(
            @SMARTQUERYSPECXML.value(
                                        '
                                        declare namespace bbfa="bb_appfx_smartquery";
                                        declare namespace c="bb_appfx_commontypes";
                                        /bbfa:SmartQuerySpec[1]/@c:SecurityUIFolder
                                        '
                                        ,'nvarchar(255)')
                ,'');


    --Add record type if it does not exist.

    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RECORDTYPEID output;

    if @EXISTINGID is null 

        begin
            if @ID is null set @ID = NewID();

            insert into dbo.SMARTQUERYCATALOG (ID, [NAME], DESCRIPTION, TVFNAME, PRIMARYKEYFIELD, TVFPARAMETERLIST, RECORDTYPEID, FILTERFORMDEFINITION, OUTPUTDEFINITION, SMARTQUERYSPEC, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYUIFOLDER, HASAPPUSERIDPARAM, SPECUINAME, MARTKEY, TVFSCHEMA)
                values (@ID, @NAME, @DESCRIPTION, @TVFNAME, @PRIMARYKEYFIELD, @TVFPARAMETERLIST, @RECORDTYPEID, @FILTERDEFXML, @OUTPUTDEFXML, @SMARTQUERYSPECXML, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), @SECURITYUIFOLDER, @HASAPPUSERIDPARAM, @SPECUINAME, @MARTKEY, @TVFSCHEMA);
        end

    else
    begin
        --Bug 438143:BB753056: error: conversion failed when converting from a character string to unique identifier when including a User Defined Smart Query selection in an Export Process

        --Check for selections that use this smart query. If there are, do not allow primary key field to be blank or a non-guid field.

        --We only need to check on update because, when adding a new smart query, there is nothing to break; it is valid to write a smart query

        --with a blank or non-guid primary key as long as it is not used for a selection.

        --A selection cannot be created if the primary key is set incorrectly.

        if exists
        (
            select
                1
            from
                dbo.SMARTQUERYCATALOG
                inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
                inner join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
                inner join dbo.IDSETREGISTER on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
            where
                SMARTQUERYCATALOG.ID = @ID
        )
        begin

            --Check for blank primary key field

            if (nullif(@PRIMARYKEYFIELD, N'') is null)
            begin

                begin
                    --There is a selection based on this smart query. The primary key cannot be removed.

                    raiserror('You must include a system record ID as the primary key so query instances created from this definition can uniquely identify records. System record IDs appear in the list of primary key options when you include them as output fields in the ad-hoc query you use to create the definition. Note, for a constituent view smart query, include the "Constituent record" field. For a revenue view smart query, include the "Revenue record" field.',13,1);
                end
            end
            --Check for non-guid primary key field

            else
            begin

                declare @PRIMARYKEYDATATYPE nvarchar(100);

                with xmlnamespaces('bb_appfx_smartquery' as ns)
                select
                    @PRIMARYKEYDATATYPE = SPECXML.NODE.value('@DataType','nvarchar(100)')
                from
                    @SMARTQUERYSPECXML.nodes('ns:SmartQuerySpec/ns:Output/ns:OutputFields/ns:OutputField') SPECXML(NODE)
                where
                    SPECXML.NODE.value('@Name','nvarchar(100)') = @PRIMARYKEYFIELD

                if @PRIMARYKEYDATATYPE <> N'Guid'
                begin
                    --There is a selection based on this smart query. The primary key must be an ID field.

                    raiserror('You must include a system record ID as the primary key so query instances created from this definition can uniquely identify records. System record IDs appear in the list of primary key options when you include them as output fields in the ad-hoc query you use to create the definition. Note, for a constituent view smart query, include the "Constituent record" field. For a revenue view smart query, include the "Revenue record" field.',13,1);
                end
            end
        end

        update dbo.SMARTQUERYCATALOG
        set 
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            RECORDTYPEID = @RECORDTYPEID,
            TVFNAME = @TVFNAME,
            PRIMARYKEYFIELD = @PRIMARYKEYFIELD,
            TVFPARAMETERLIST = @TVFPARAMETERLIST,
            FILTERFORMDEFINITION = @FILTERDEFXML,
            OUTPUTDEFINITION = @OUTPUTDEFXML,
            STATICPARAMETERDEFINITION = null,
            SMARTQUERYSPEC = @SMARTQUERYSPECXML,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate(),
            SECURITYUIFOLDER = @SECURITYUIFOLDER,
            HASAPPUSERIDPARAM = @HASAPPUSERIDPARAM,
            SPECUINAME = @SPECUINAME,
            MARTKEY = @MARTKEY,
            TVFSCHEMA = @TVFSCHEMA
        where ID = @EXISTINGID;

    end

end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch;

if @@error <> 0 return 3;

return 0;