USP_SITEIMAGE_BULKUPDATE

The save procedure used by the add dataform template "SiteImage Bulk Update Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@IMAGES	xml	IN	Images
@CATEGORYIDS	xml	IN	Image category Ids
@APPROVED	bit	IN	Approved

Definition

Copy


CREATE procedure [dbo].[USP_SITEIMAGE_BULKUPDATE]
                (
                @ID uniqueidentifier = null output,
                @IMAGES            xml = null,
                @CATEGORYIDS    xml = null,
                @APPROVED        bit
                )
as
begin

                begin transaction

                -- parse the image ID list            
                declare @ImageEditTable table (ID int, NAME nvarchar(256), CAPTION nvarchar(256), URL nvarchar(256))
                insert into @ImageEditTable (ID, NAME, CAPTION, URL) 
                (    select IMS.IM.value('@id', 'int'), IMS.IM.value('@name', 'nvarchar(256)'), IMS.IM.value('@caption', 'nvarchar(256)'), IMS.IM.value('@url', 'nvarchar(256)')
                    from @IMAGES.nodes('/images/image') IMS(IM)
                )

                -- set the image approval
                update SI
                set SI.Approved = @APPROVED,
                    SI.NAME = IET.NAME,
                    SI.CAPTION =  IET.CAPTION,
                    SI.URL=IET.URL
                from [dbo].[SiteImages] si
                join @ImageEditTable IET
                on SI.ID = IET.ID

                -- clear existing image categories
                delete from SiteImageFolders
                where SiteImageID in (select ID from @ImageEditTable)

                -- create new image categories
                insert into dbo.SiteImageFolders(SiteImageID, SiteFolderID)
                select IET.ID, CATEGORIES.ID.value('.', 'int') from @ImageEditTable IET
                cross join @CATEGORYIDS.nodes('/categories/id') CATEGORIES(ID)

                commit transaction

                return 0
end