44 lines
3.1 KiB
Transact-SQL
44 lines
3.1 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[ocmd_merge_properties]
|
|
@newprop [dbo].[ocms_type_properties_base] READONLY
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
WITH np as (
|
|
-- make sure properties are allowed by templates
|
|
SELECT
|
|
n.*
|
|
,tp.[type]
|
|
FROM @newprop as n
|
|
JOIN [dbo].[ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid]
|
|
JOIN [dbo].[ocms_templates] as t ON i.[template_id] = t.[ocms_tid]
|
|
JOIN [dbo].[ocms_template_properties] as tp ON t.[ocms_tid] = tp.[ocms_tid] AND n.[key] = tp.[key]
|
|
)
|
|
MERGE [dbo].[ocms_properties] as TARGET
|
|
USING np as SOURCE ON TARGET.[key] = SOURCE.[key] and TARGET.[locale] = SOURCE.[locale] AND TARGET.[ocms_iid] = SOURCE.[ocms_iid]
|
|
WHEN MATCHED AND ISNULL(SOURCE.[value], '') = '' AND SOURCE.[type] <> 'array' THEN
|
|
DELETE
|
|
WHEN MATCHED AND TARGET.[Value] <> SOURCE.[value] THEN
|
|
UPDATE SET [value] = SOURCE.[value], [DateModified] = GETUTCDATE()
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT ([ocms_iid], [key], [value], [locale], [DateCreated], [DateModified])
|
|
VALUES (SOURCE.[ocms_iid], SOURCE.[key], SOURCE.[value], SOURCE.[locale], GETUTCDATE(), GETUTCDATE());
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocmd_merge_properties] TO SCHEMA OWNER
|
|
GO
|