July 18, 2016 at 10:41 am
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.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply