55 lines
3.3 KiB
Transact-SQL
55 lines
3.3 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_setItemOrder] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP PROCEDURE [dbo].[ocms_setItemOrder]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_setItemOrder] Script Date: 02.12.2020 21:05:06 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[ocms_setItemOrder]
|
|
@ocms_iid_csv varchar(2000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @neworder TABLE([ocms_iid] bigint, [order] int);
|
|
DECLARE @c char(1) = ','; --delimiter
|
|
|
|
INSERT INTO @neworder
|
|
SELECT value, idx = ROW_NUMBER() OVER (ORDER BY CHARINDEX(@c + value + @c, @c + @ocms_iid_csv + @c))
|
|
FROM STRING_SPLIT(@ocms_iid_csv, ',');
|
|
|
|
DECLARE @parents TABLE([parent_iid] bigint);
|
|
INSERT INTO @parents
|
|
SELECT DISTINCT [parent_iid]
|
|
FROM [dbo].[ocms_items] as i
|
|
JOIN @neworder as no ON i.[ocms_iid] = no.[ocms_iid]
|
|
WHERE i.[parent_iid] is not null;
|
|
|
|
select * from @parents
|
|
|
|
IF (SELECT COUNT(*) FROM @parents) <> 1
|
|
THROW 61010, 'order can only be set for exact one parent', 1;
|
|
|
|
DECLARE @parent_iid bigint = (SELECT TOP(1) [parent_iid] FROM @parents);
|
|
|
|
UPDATE i
|
|
SET [order] = ISNULL(no.[order], 999)
|
|
OUTPUT
|
|
inserted.[ocms_iid], inserted.[order]
|
|
FROM [dbo].[ocms_items] as i
|
|
LEFT JOIN @neworder as no ON i.[ocms_iid] = no.[ocms_iid]
|
|
WHERE i.[parent_iid] = @parent_iid;
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_setItemOrder] TO SCHEMA OWNER
|
|
GO
|