USP_ADHOCQUERY_CREATEORUPDATEDEFINITION

Create or update ad-hoc query definition

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@QUERYVIEWCATALOGID	uniqueidentifier	IN
@NAME	nvarchar(255)	IN
@DESCRIPTION	nvarchar(1024)	IN
@QUERYDEFINITIONXML	xml	IN
@QUERYCATEGORYCODEID	uniqueidentifier	IN
@DATAFORMINSTANCEID	uniqueidentifier	IN
@OTHERSCANMODIFY	bit	IN
@APPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
@FOLDERID	uniqueidentifier	IN
@SECURITYLEVEL	tinyint	IN
@PERMISSIONEDROLES	xml	IN
@SECURITYLEVELEDIT	tinyint	IN
@PERMISSIONEDROLESEDIT	xml	IN
@MOBILIZE	bit	IN
@ISFAVORITE	bit	IN

Definition

Copy



CREATE procedure [dbo].[USP_ADHOCQUERY_CREATEORUPDATEDEFINITION]
(
    @ID uniqueidentifier,
    @QUERYVIEWCATALOGID uniqueidentifier,
    @NAME nvarchar(255),
    @DESCRIPTION nvarchar(1024),
    @QUERYDEFINITIONXML xml,
    @QUERYCATEGORYCODEID uniqueidentifier,
    @DATAFORMINSTANCEID uniqueidentifier,
    @OTHERSCANMODIFY bit,
    @APPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @SITEID uniqueidentifier = null,
    @FOLDERID uniqueidentifier = null,
    @SECURITYLEVEL tinyint = null,
    @PERMISSIONEDROLES xml = null,
    @SECURITYLEVELEDIT tinyint = null,
    @PERMISSIONEDROLESEDIT xml = null,
    @MOBILIZE bit = null,
    @ISFAVORITE bit = null
)
as

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

declare @EXISTINGID uniqueidentifier;

if @QUERYCATEGORYCODEID = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)
    set @QUERYCATEGORYCODEID = null;

if @DATAFORMINSTANCEID = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)
    set @DATAFORMINSTANCEID = null;

if @FOLDERID = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)
    set @FOLDERID = null;

set @SECURITYLEVEL = coalesce(@SECURITYLEVEL, 0)
set @SECURITYLEVELEDIT = coalesce(@SECURITYLEVELEDIT, 0)

select @EXISTINGID = ADHOCQUERY.ID
from dbo.ADHOCQUERY 
where ADHOCQUERY.ID = @ID;

declare @ISBROWSABLE bit;
select
    @ISBROWSABLE = dbo.UFN_ADHOCQUERY_ISBROWSABLE(@QUERYDEFINITIONXML, OBJECTNAME, PRIMARYKEYFIELD)
from
    dbo.QUERYVIEWCATALOG
where
    ID = @QUERYVIEWCATALOGID

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if @EXISTINGID is null
begin
    insert into dbo.ADHOCQUERY
    (
        ID, 
        QUERYVIEWCATALOGID, 
        NAME, 
        DESCRIPTION, 
        QUERYDEFINITIONXML, 
        QUERYCATEGORYCODEID, 
        DATAFORMINSTANCEID, 
        OTHERSCANMODIFY, 
        OWNERID, 
        SITEID, 
        FOLDERID, 
        SECURITYLEVEL,
        SECURITYLEVELEDIT,
        MOBILIZE,
        ISBROWSABLE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values 
    (
        @ID, 
        @QUERYVIEWCATALOGID, 
        @NAME, 
        @DESCRIPTION, 
        @QUERYDEFINITIONXML, 
        @QUERYCATEGORYCODEID, 
        @DATAFORMINSTANCEID, 
        @OTHERSCANMODIFY, 
        @APPUSERID, 
        @SITEID, 
        @FOLDERID, 
        @SECURITYLEVEL,
        @SECURITYLEVELEDIT,
        coalesce(@MOBILIZE, 0),
        coalesce(@ISBROWSABLE, 0),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    );
end
else
begin
    update dbo.ADHOCQUERY set 
        QUERYVIEWCATALOGID = @QUERYVIEWCATALOGID, 
        NAME = @NAME, 
        DESCRIPTION = @DESCRIPTION, 
        QUERYDEFINITIONXML = @QUERYDEFINITIONXML, 
        QUERYCATEGORYCODEID = @QUERYCATEGORYCODEID, 
        DATAFORMINSTANCEID = @DATAFORMINSTANCEID, 
        OTHERSCANMODIFY = @OTHERSCANMODIFY, 
        SITEID = @SITEID, 
        FOLDERID = @FOLDERID,
        SECURITYLEVEL = @SECURITYLEVEL,
        SECURITYLEVELEDIT = @SECURITYLEVELEDIT,
        MOBILIZE = coalesce(@MOBILIZE, MOBILIZE),
        ISBROWSABLE = coalesce(@ISBROWSABLE, ISBROWSABLE),
        CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where ID = @ID;

    if @SECURITYLEVEL = 0 or @SECURITYLEVELEDIT = 0
    begin
        declare @contextCache varbinary(128);
        set @contextCache = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;                    

        -- Clear specific roles if all roles are allowed now

        if @SECURITYLEVEL = 0
            delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE where ADHOCQUERYID = @ID

        if @SECURITYLEVELEDIT = 0
            delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT where ADHOCQUERYID = @ID

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;
    end
end

-- Update the role permissions for this query

if @SECURITYLEVEL = 1
begin
    exec dbo.USP_ADHOCQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @ID, @PERMISSIONEDROLES, @CHANGEAGENTID, @CURRENTDATE
end


if @SECURITYLEVELEDIT = 1
begin
    exec dbo.USP_ADHOCQUERYINSTANCE_GETSYSTEMROLEEDITPERMISSIONSLIST_UPDATEFROMXML @ID, @PERMISSIONEDROLESEDIT, @CHANGEAGENTID, @CURRENTDATE
end

if @ISFAVORITE = 1
begin
    exec dbo.USP_APPUSERADHOCQUERYFAVORITE_ADD @ID, @APPUSERID, @CHANGEAGENTID
end
else
begin
    exec dbo.USP_APPUSERADHOCQUERYFAVORITE_DELETE @ID, @APPUSERID, @CHANGEAGENTID
end