July 15, 2016 at 10:28 am
I am writing a stored procedure that receives two values from an ms access interface.
ALTER PROCEDURE dbo.Test_Insert_ArrangementFonts
(
@ArrangementIDVARCHAR(15)
,@FontNameVARCHAR(15)
)
The procedure's job is to insert rows for an Arrangement Id and its related fonts to a data table Arrangement_Fonts. An Arrangement Id can have multiple Fonts.
Example:
0164781 Arial
0164781 Times
0164781 Tahoma
0164781 Verdana
In the example above, Arrangement Id 0164781 has 4 different Fonts related to it.
At times, the stored procedure can be called again, using the same Arrangement Id. It might've been that an edit was needed to be done, and one, or some of the fonts inserted the first time it, need to be removed from the table for that Arrangement Id.
So the example above, after the stored procedure is ran a second time would look like:
0164781 Arial
0164781 Times
I am including 2 scripts, one for creating the target table, and one with the stored procedure I have written but is not working as I needed to.
What the attached script currently does is the following:
table is empty, ran sp first time:
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Arial';
'MN0164781','Arial' -- gets inserted
run sp again:
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Times';
'MN0164781','Arial' -- gets deleted
'MN0164781','Times' -- does not get inserted
run sp again:
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Tahoma';
'MN0164781','Tahoma' -- gets inserted
run sp again:
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Verdana';
'MN0164781','Tahoma' -- gets deleted
'MN0164781','Verdana' -- does not get inserted
If I could get help to figure this thing out, that would be awesome.
Thank you.
July 15, 2016 at 11:28 am
Your insert is in the else section of the if, so if a row already exists then the insert never gets called.
I'm not sure what you are trying to do - do you want multiple fonts per Arrangementid cause what you have now is deleting all fonts for an arrangement ID except if the font name matches what is passed in.
July 15, 2016 at 11:41 am
Why not use MERGE to do an UPSERT and forget about deleting rows? Just insert or update depending on if a row exists or not.
Or if you don't want to use MERGE, you can still use update and insert.
UPDATE dbo.Arrangement_Fonts SET
FontName = @FontName
WHERE ArrangementID = @ArrangementID;
INSERT INTO dbo.Arrangement_Fonts
(
ArrangementID
,FontName
,DateAdded
)
VALUES
(
@ArrangementID
,@FontName
,GETDATE()
)
WHERE NOT EXISTS
(
SELECT1
FROMdbo.Arrangement_Fonts
WHERE ArrangementID = @ArrangementID
);
July 15, 2016 at 11:50 am
Yes, I want to be able to insert multiple fonts per Arrangement Id.
I also want to be able to delete fonts that are no longer needed, per Arrangement Id.
I updated the stored procedure. If you can, please create the table and sp using the new scripts attached (create_table, create_sp respectively). Then execute sp in this order so you can see what i am talking about:
-- 1st run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Doremi';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Jive';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','JiveTalk';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Helvetica';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Times';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Trebuchet';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Arial';
--results - good!
--MN0164781Doremi
--MN0164781Jive
--MN0164781JiveTalk
--MN0164781Helvetica
--MN0164781Times
--MN0164781Trebuchet
--MN0164781Arial
--2nd run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Doremi';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Arial';
--results --good!
--MN0164781Doremi
--MN0164781Arial
--3rd run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Times';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Trebuchet';
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Arial';
--results - wrong!
--MN0164781Arial
--results should be
--MN0164781,Times
--MN0164781,Trebuchet
--MN0164781,Arial
July 15, 2016 at 11:55 am
I need to be able to delete because this scenario can happen, and perhaps I am not understanding correctly but let me explain.
Arrangement Id MN0164781 has 5 Fonts in the beginning: Doremi, Jive, JiveTalk, Helvetica, Times
But then a request is submitted to remove 2 font types for the same Arrangement Id MN0164781, so it would end up only with: Doremi, Jive, JiveTalk
I think a delete is needed otherwise I would still end up with 5. Right?
Also, with the same token, a request is submitted to add more fonts to the same Arrangement Id.
I do not think my stored procedure is handling that correctly.
July 15, 2016 at 12:02 pm
I tried your code and it gives me same results as the stored procedure I attached.
I ran the same steps as I posted before a couple of entries ago, and I end up with only one row after the last execute.
July 15, 2016 at 12:03 pm
Then maybe, you should create one stored procedure to delete and one to insert.
If you'll always have an array that will have the rows that must remain stored, then you should try using a table valued parameter to delete and insert (not "delete or insert").
July 15, 2016 at 12:08 pm
I'm confused by your logic.
You have font Doremi in the table. You pass this font again and the sp deletes every font other than Doremi. You say this is correct.
Later on you have font Arial in your table. You pass this font in again and the sp deletes every font other than Arial yet you now say this is incorrect.
July 15, 2016 at 12:08 pm
I am unfamiliar with table valued parameters, my experience with T-SQL is not vast, and thought this was an easy to do task even with limited skills. Is there a chance that you can show me how to figure this out? Thank you.
July 15, 2016 at 12:17 pm
Yes, the first pass it includes all 7 fonts.
Then I pass 2 fonts that I want to keep, which are Doremi, and Arial, every other gets deleted.
Then I pass 3 fonts that I want to keep, one already exists Arial, and two that don't exist, Trebuchet, and Times. Arial stays, that is good, but the two other that don't exist, have to be inserted leaving the table with Times, Trebuchet, and Arial
July 15, 2016 at 12:17 pm
Yes, the first pass it includes all 7 fonts.
Then I pass 2 fonts that I want to keep, which are Doremi, and Arial, every other gets deleted.
Then I pass 3 fonts that I want to keep, one already exists Arial, and two that don't exist, Trebuchet, and Times. Arial stays, that is good, but the two other that don't exist, have to be inserted leaving the table with Times, Trebuchet, and Arial
July 15, 2016 at 12:23 pm
Your passing them in separate executions though. When you pass Arial in for the 2nd time, it doesn't know you also want to keep the other fonts.
Luis has the best advise - separate sp for Delete and Insert.
July 15, 2016 at 12:30 pm
can the second sp be called from within the first sp?
July 15, 2016 at 1:00 pm
So the problem seems to be you are calling the sp mulitple times in what you call a run and expecting the database to know what else has been inserted or deleted in that "run".
So how about we call the procedure once and pass all the font names in using a comma separated string. This way we will know exactly what should be deleted and what should be inserted. See an example below.
Check out this link for details on the DelimitedSplit8k function. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
ALTER PROCEDURE dbo.mn_Insert_ArrangementFonts (@ArrangementID VARCHAR(15)
, @FontNames VARCHAR(500)
)
AS
BEGIN
DELETE af
FROM dbo.Arrangement_Fonts af
LEFT JOIN dbo.DelimitedSplit8K(@FontNames, ',') split
ON af.FontName = split.Item
WHERE split.ItemNumber IS NULL;
INSERT INTO dbo.Arrangement_Fonts
(ArrangementID
, FontName
, DateAdded)
SELECT @ArrangementID
, Item
, GETDATE()
FROM dbo.DelimitedSplit8K(@FontNames, ',')
WHERE NOT EXISTS ( SELECT 1
FROM dbo.Arrangement_Fonts
WHERE FontName = Item );
END;
You would separate the font names with commas.
-- 1st run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Doremi,Jive,JiveTalk,Helvetica,Times,Trebuchet,Arial';
/*results
MN0164781Doremi
MN0164781Jive
MN0164781JiveTalk
MN0164781Helvetica
MN0164781Times
MN0164781Trebuchet
MN0164781Arial*/
--2nd run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Doremi,Arial';
/*results
MN0164781Doremi
MN0164781Arial*/
--3rd run
EXEC[dbo].[mn_Insert_ArrangementFonts] 'MN0164781','Times,Trebuchet,Arial';
/*results
MN0164781Arial
MN0164781Times
MN0164781Trebuchet*/
July 15, 2016 at 1:02 pm
itortu (7/15/2016)
can the second sp be called from within the first sp?
Yes, but that's not the point.
You'll delete once and insert multiple times. That's the way to call them from the application.
The only way to call the insert a single time and so they can be done in a single procedure, is that you use table valued parameters. I'm not telling you how to do that because there are many articles and guides on the internet that would explain it better than I could in a forum post.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply