I am following up your recommendation that is to pass a comma separated string with the values for the fonts I need. I format the string in the front end (VBA) and pass it to the stored procedure, along with the arrangementId.
In the stored procedure then I use a split function to split the values and delete/ insert accordingly.
Test VBA code:
arrFontNames = Array("Doremi", "Times-Roman", "Helvetica", "Jivetalk", "Jive")
strFonts = "'" & Join(arrFontNames, ",") & "'"
strArrId = '0164781'
UDF:
ALTER FUNCTION [dbo].[fn_split]
(
@param NVARCHAR(MAX)
,@delimiter CHAR(1)
)
RETURNS @t TABLE
(
val NVARCHAR(MAX)
,seq INT
)
AS
BEGIN
SET @param+=@delimiter;
WITH a
AS (
SELECT
CAST(1 AS BIGINT)f
, CHARINDEX(@delimiter, @param)t
, 1seq
UNION ALL
SELECT
t + 1
, CHARINDEX(@delimiter, @param, t+1)
, seq + 1 FROM
a WHERE CHARINDEX(@delimiter, @param, t+1) > 0)
INSERT INTO @t
SELECT
SUBSTRING(@param, f, t-f)
,seq FROM
a OPTION(
MAXRECURSION 0);
RETURN;
END;
Stored Procedure (the code you kindly posted):
ALTER PROCEDURE dbo.mn_Insert_ArrangementFonts(
@ArrangementID VARCHAR(15)
, @Fonts VARCHAR(100))
AS
BEGIN
DELETE af
FROM dbo.Arrangement_Fonts af
LEFT JOIN
(
SELECT val
FROM dbo.fn_split( @Fonts, ',' )
) split
ON af.FontName = split.val
WHERE split.val IS NULL;
INSERT INTO dbo.Arrangement_Fonts
(
ArrangementID
,FontName
,DateAdded
)
SELECT
@ArrangementID
, nv.val
, GETDATE()
FROM
(
SELECT
val FROM
dbo.fn_split( @Fonts, ',' )
) nv
WHERE NOT EXISTS
(
SELECT
1 FROM
dbo.Arrangement_Fonts WHERE FontName = nv.val
);
END;
I so far tested 3 times only and directly in SQL:
1st execution:
EXEC[dbo].[mn_Insert_ArrangementFonts] '0164781','Doremi,Jive,JiveTalk,Helvetica,Times,Trebuchet,Arial';
Results:
Doremi
Jive
JiveTalk
Helvetica
Times
Trebuchet
Arial
2nd execution:
EXEC[dbo].[mn_Insert_ArrangementFonts] '0164781','Doremi,Jive,JiveTalk';
Restuls:
Doremi
Jive
JiveTalk
3rd execution:
EXEC[dbo].[Test_Insert_ArrangementFonts] '0164781','Helvetica,Times,Trebuchet,Arial';
Results:
Helvetica
Times
Trebuchet
Arial
So far so good. Now I just need to complete the VBA code to call the stored procedure passing the required parameters.