<link href="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<!------ Include the above in your HEAD tag ---------->
<div class="container">
<div class="row">
<h2>Create your snippet's HTML, CSS and Javascript in the editor tabs</h2>
</div>
</div>
ALTER PROCEDURE [dbo].[wfs_UpdateFeatureGrowthDrivers]
@FeatureID INT
, @KeyGrowthDriverIDString varchar(max)
, @CreateUserID INT
AS
BEGIN
--Verify a FeatureID was passed in
IF @FeatureID is NOT NULL
BEGIN
BEGIN TRANSACTION
--Remove all old rows from wfs_FeatureGrowthDrivers for the current FeatureID
DELETE FROM [dbo].[wfs_FeatureGrowthDrivers] WHERE FeatureID = @FeatureID;
--Loop recursively through the KeyGrowthDriverIDString string to separate our our KeyGrowthDriverID integrer values
WITH FeatureGrowthDriverMapping(
[FeatureID]
,[CreateUserID]
, [KeyGrowthDriverID]
, String) AS
(SELECT
a.FeatureID
,a.[CreateUserID]
, CONVERT(int, LEFT([KeyGrowthDriverIDString], CHARINDEX(',', [KeyGrowthDriverIDString] + ',') - 1)) AS Expr1
, STUFF([KeyGrowthDriverIDString], 1, CHARINDEX(',', [KeyGrowthDriverIDString] + ','), '') AS Expr2
FROM (SELECT @FeatureID [FeatureID], @KeyGrowthDriverIDString [KeyGrowthDriverIDString], @CreateUserID [CreateUserID]) a
UNION ALL
SELECT
FeatureID
,[CreateUserID]
, CONVERT(int, LEFT(String, CHARINDEX(',', String + ',') - 1)) AS Expr1
, STUFF(String, 1, CHARINDEX(',', String + ','), '') AS Expr2
FROM FeatureGrowthDriverMapping
WHERE (String > ''))
--Insert new rows into wfs_FeatureGrowthDrivers with one row for each KeyGrowthDriverID integrer value
INSERT INTO [dbo].[wfs_FeatureGrowthDrivers]
(
[FeatureID]
,[GrowthDriverID]
,[CreateDate]
,[CreateUserID]
)
SELECT DISTINCT [FeatureID]
,[KeyGrowthDriverID]
,GETDATE()
,[CreateUserID]
FROM FeatureGrowthDriverMapping
WHERE KeyGrowthDriverID>0
COMMIT
END
BEGIN TRANSACTION
DECLARE @old table (Item INT)
INSERT INTO @old SELECT PlatformSeq FROM vw_wfs_FeaturePlatformEventsLatestActive WHERE FeatureID = @FeatureID
DECLARE @new table (Item INT)
INSERT INTO @new SELECT Item FROM dbo.SplitStrings_CTE(@PlatformSeqs,',')
-- insert active events for newly active things
INSERT INTO dbo.wfs_FeaturePlatformEvents
(
FeatureID,
PlatformSeq,
isActive,
EventUserID
)
SELECT
@FeatureID,
Item,
1,
@CreateUserID
FROM @new
WHERE Item NOT IN (SELECT Item from @old)
-- insert inactive events for newly removed things
INSERT INTO dbo.wfs_FeaturePlatformEvents
(
FeatureID,
PlatformSeq,
isActive,
EventUserID
)
SELECT
@FeatureID,
Item,
0,
@CreateUserID
FROM @old
WHERE Item NOT IN (SELECT Item from @new)
COMMIT
ALTER FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
ALTER FUNCTION [dbo].[SplitStrings_CTE]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255) = ','
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @StringLength INT = LEN(@List) + 1, @DelimiterLength INT = LEN(@Delimiter);
WITH a AS
(
SELECT
[start] = 1,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @StringLength),
[value] = SUBSTRING(@List, 1,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @StringLength) - 1)
UNION ALL
SELECT
[start] = CONVERT(INT, [end]) + @DelimiterLength,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @DelimiterLength), 0), @StringLength),
[value] = SUBSTRING(@List, [end] + @DelimiterLength,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @DelimiterLength), 0), @StringLength)-[end]-@DelimiterLength)
FROM a
WHERE [end] < @StringLength
)
INSERT @Items SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);
RETURN;
END
