USP_EXPORTPROCESS_ADD

CREATE procedure dbo.USP_EXPORTPROCESS_ADD
(
    @ID uniqueidentifier = null output,
    @ADHOCQUERYID uniqueidentifier = null,
    @SMARTQUERYINSTANCEID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(255) = '',
    @DESCRIPTION nvarchar(1000) = '',
    @EXPORTDEFINITIONID uniqueidentifier = null, 
    @SELECTIONID uniqueidentifier = null
)
with execute as caller
as
    set nocount on;

    if @ID is null
        set @ID = newid();

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

    begin try

        --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

        --When we are adding an export process based on a selection, make sure that the selection uses a guid as its ID field.

        --We do not need to check for a blank primary key field because you cannot create a selection without specifying a primary key field.

        if @SELECTIONID is not null and exists
        (
            --we only care about smart query selections for this check

            select
                1
            from
                dbo.IDSETREGISTER
                inner join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
            where
                IDSETREGISTER.ID = @SELECTIONID
        )
        begin

            declare @DATATYPE nvarchar(50);
            declare @SELECTIONOBJECT nvarchar(128);

            with xmlnamespaces('bb_appfx_smartquery' as ns)
            select
                @DATATYPE = SPECXML.NODE.value('@DataType','nvarchar(50)'),
                @SELECTIONOBJECT = IDSETREGISTER.DBOBJECTNAME
            from
                dbo.IDSETREGISTER
                inner join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
                inner join dbo.SMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
                inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
                cross apply SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec/ns:Output/ns:OutputFields/ns:OutputField') SPECXML(NODE)
            where
                SPECXML.NODE.value('@Name','nvarchar(128)') = SMARTQUERYCATALOG.PRIMARYKEYFIELD
                and IDSETREGISTER.ID = @SELECTIONID;

            if @DATATYPE is null or @DATATYPE <> N'Guid'
                --The selection must use a system record ID field as its primary key field.

                raiserror('BBERR_PRIMARYKEYNOTGUID : You must use a selection that includes a system record ID in the primary key field.',13,1);
            else
            begin
                --If the smart query was changed after the selection was created but the selection was not updated to reflect this,

                --there could still be a problem that we cannot find by looking at the tables.

                set @DATATYPE = null;
                select
                    @DATATYPE = t.name
                from
                    sys.all_columns c
                    inner join sys.types t on t.system_type_id = c.system_type_id
                where
                    c.name = N'ID'
                    and c.object_id = object_id(@SELECTIONOBJECT);
                if @DATATYPE <> N'uniqueidentifier'
                    --The selection must use a system record ID field as its primary key. The user-defined smart query has been updated to

                    --use a system record ID but the selection has not been updated to reflect this.

                    raiserror('BBERR_PRIMARYKEYNOTGUID : You must use a selection that includes a system record ID in the primary key field.',13,1);
            end
        end

        insert into dbo.EXPORTPROCESS
            (ID, 
                ADHOCQUERYID,
                SMARTQUERYINSTANCEID,
                NAME, 
                DESCRIPTION,
                EXPORTDEFINITIONID, 
                SELECTIONID,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
        values
            (@ID, 
                @ADHOCQUERYID,
                @SMARTQUERYINSTANCEID,
                @NAME, 
                @DESCRIPTION, 
                @EXPORTDEFINITIONID, 
                @SELECTIONID,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                getdate(),
                getdate());
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch;

    return 0;