USP_DATAFORMTEMPLATE_ADD_QUERYMOVECATEGORYADD

create procedure dbo.USP_DATAFORMTEMPLATE_ADD_QUERYMOVECATEGORYADD
(
    @ID uniqueidentifier output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CATEGORYID uniqueidentifier = null,
    @QUERIES xml = null
)
as

set nocount on;

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

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

        -- move ad-hoc queries
        update dbo.[ADHOCQUERY] set
            [QUERYCATEGORYCODEID] = @CATEGORYID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where [ID] in
    (
      select
      T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
      from @QUERIES.nodes('/QUERIES/ITEM') T(c)
    )

        -- move smart query instances
        update dbo.[SMARTQUERYINSTANCE] set
            [QUERYCATEGORYCODEID] = @CATEGORYID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where [ID] in
    (
      select
      T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
      from @QUERIES.nodes('/QUERIES/ITEM') T(c)
    )

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0