USP_MEMBERSHIPPROGRAM_CREATEQUERY

            CREATE procedure dbo.USP_MEMBERSHIPPROGRAM_CREATEQUERY(
                @MEMBERSHIPPROGRAMID uniqueidentifier,
                @PROGRAMNAME nvarchar(100),
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                set nocount on;

                declare @QUERYSPEC xml;

                begin try
                    select @QUERYSPEC = QUERYVIEWSPEC from dbo.QUERYVIEWCATALOG 
                    where ID = '3C18BE19-0B6A-4071-A6E0-8C9AA507A0EB';

                    --give new query spec a new id

                    declare @NEWID uniqueidentifier;
                    set @NEWID = NEWID();

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@ID)[1] with sql:variable("@NEWID")
                        ');

                    --replace the name of the query view

                    declare @QUERYNAME nvarchar(150);
                    set @QUERYNAME = @PROGRAMNAME + ' Memberships';

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@Name)[1] with sql:variable("@QUERYNAME")
                        ');

                    --replace the description

                    declare @DESCRIPTION nvarchar(max);
                    set @DESCRIPTION = 'This provides the ability to query for memberships in ' + @PROGRAMNAME + '.';

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@Description)[1] with sql:variable("@DESCRIPTION")
                        ');

                    --replace view name

                    declare @VIEWNAME nvarchar(100);
                    set @VIEWNAME = 'V_QUERY_MEMBERSHIP_' + REPLACE(convert(nvarchar(36),@MEMBERSHIPPROGRAMID),'-','');

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/ViewImplementation/@ViewName)[1] with sql:variable("@VIEWNAME")
                        ');

                    --add related query views

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                                insert 
                                    <AddToParentView
                                    ParentView="V_QUERY_CONSTITUENT"
                                    ParentViewRelatedField="ID"
                                    Field="CONSTITUENTID"
                                    PathAlias="@QUERYNAME"
                                    Cardinality="OneToOptionalOne"/> 
                                    into (/QueryViewSpec/RelationshipOperations)[1]')

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                                insert
                                    <AddToParentView
                                    ParentView="V_QUERY_CONSTITUENTMARKETING"
                                    ParentViewRelatedField="CONSTITUENTID"
                                    Field="CONSTITUENTID"
                                    PathAlias="@QUERYNAME"
                                    Cardinality="OneToOptionalOne"/>
                                    into (/QueryViewSpec/RelationshipOperations)[1]')

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/RelationshipOperations/AddToParentView[1]/@PathAlias)[1] with sql:variable("@QUERYNAME")
                        ');

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/RelationshipOperations/AddToParentView[2]/@PathAlias)[1] with sql:variable("@QUERYNAME")
                        ');

                    set @QUERYSPEC = REPLACE(convert(nvarchar(max), @QUERYSPEC), '/*WHERECLAUSE*/', 'where MEMBERSHIP.MEMBERSHIPPROGRAMID = ''' + convert(nvarchar(36), @MEMBERSHIPPROGRAMID) + '''')

                    --load it

                    exec dbo.USP_LOADSPEC @QUERYSPEC, @CHANGEAGENTID    

                    declare @MEMBERSHIPQUERYSYSTEMPRIVILEGE uniqueidentifier = '2ed86a50-d16a-469e-b167-d486a5efff5d';

                    exec dbo.USP_SYSTEMROLE_COPYSYSTEMPRIVILEGEPERMISSIONSTOQUERYVIEW @NEWID, @MEMBERSHIPQUERYSYSTEMPRIVILEGE, @CHANGEAGENTID;
                    exec dbo.USP_MEMBERSHIP_UPDATEATTRIBUTEQUERIES @CHANGEAGENTID;

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