Try to use MERGE:
CREATE TABLE TestProjectTemplateSection(
ProjectID int,
ProjectTemplateID int,
SectionID int,
IsActive bit,
SectionOrderNumber int
)
INSERT TestProjectTemplateSection(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)VALUES
(1,1,1,1,4),
(1,1,2,0,2),
(1,1,3,1,3)
And a procedure to insert/update/deactivate with:
CREATE PROC SetTestProjectTemplateSection
@ProjectID int,
@ProjectTemplateID int,
@SectionID int,
@IsActive int=1,
@SectionOrderNumber int=NULL
AS
MERGE TestProjectTemplateSection trg
USING
(
SELECT
@ProjectID ProjectID,
@ProjectTemplateID ProjectTemplateID,
@SectionID SectionID,
@IsActive IsActive,
@SectionOrderNumber SectionOrderNumber
) src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN -- update IsActive and SectionOrderNumber
UPDATE SET
trg.IsActive=src.IsActive,
trg.SectionOrderNumber=ISNULL(src.SectionOrderNumber,trg.SectionOrderNumber) -- if NULL then not change it
WHEN NOT MATCHED BY TARGET THEN -- insert new row
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive,src.SectionOrderNumber);
GO
Demo:
-- case 1 - update
EXEC SetTestProjectTemplateSection 1,1,2,1
EXEC SetTestProjectTemplateSection 1,1,2,1,111 -- set new order number
SELECT *
FROM TestProjectTemplateSection
-- case 2 - insert
EXEC SetTestProjectTemplateSection 2,1,2,1,123
SELECT *
FROM TestProjectTemplateSection
-- case 3 - deactive
EXEC SetTestProjectTemplateSection 2,1,2,0
SELECT *
FROM TestProjectTemplateSection
I think as variant you can use STRING_SPLIT for the list of sections (see @ListOfSectionID):
CREATE PROC SetTestProjectTemplateSections
@ProjectID int,
@ProjectTemplateID int,
@ListOfSectionID varchar(100), -- e.g. '1,2,3,4'
@IsActive int=1
AS
MERGE TestProjectTemplateSection trg
USING
(
SELECT
@ProjectID ProjectID,
@ProjectTemplateID ProjectTemplateID,
CAST(value AS int) SectionID,
@IsActive IsActive
FROM STRING_SPLIT(@ListOfSectionID,',')
) src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN
UPDATE SET
trg.IsActive=src.IsActive
WHEN NOT MATCHED BY TARGET THEN
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive);
GO
But STRING_SPLIT works for SQL Server 2017. For another version of SQL Server you can find a user defined function for splitting. For example - T-SQL split string
Test:
EXEC SetTestProjectTemplateSections 1,1,'1,2,3,4',0
SELECT *
FROM TestProjectTemplateSection
As variant you can use a table parameter:
CREATE TYPE TypeForProjectTemplateSection AS table(
ProjectID int,
ProjectTemplateID int,
SectionID int,
IsActive bit,
SectionOrderNumber int
)
GO
And use this type in your procedure:
CREATE PROC SetTestProjectTemplateSectionFromTable
@ProjectTemplateSectionDate dbo.TypeForProjectTemplateSection READONLY -- need to use READONLY here
AS
MERGE TestProjectTemplateSection trg
USING @ProjectTemplateSectionDate src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN -- update IsActive and SectionOrderNumber
UPDATE SET
trg.IsActive=src.IsActive,
trg.SectionOrderNumber=src.SectionOrderNumber
WHEN NOT MATCHED BY TARGET THEN -- insert new row
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive,src.SectionOrderNumber);
GO
How to use it:
DECLARE @Table dbo.TypeForProjectTemplateSection
INSERT @Table(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)VALUES
(1,1,1,1,111),
(1,1,2,1,222)
EXEC SetTestProjectTemplateSectionFromTable @Table -- use it as parameter
SELECT *
FROM TestProjectTemplateSection
And you can use it from C#:
SqlCommand cmd = new SqlCommand("SetTestProjectTemplateSectionFromTable", con);
cmd.CommandType = CommandType.StoredProcedure;
DataTable tbl = new DataTable();
tbl.Columns.Add("ProjectID", typeof(int));
tbl.Columns.Add("ProjectTemplateID", typeof(int));
tbl.Columns.Add("SectionID", typeof(int));
tbl.Columns.Add("IsActive", typeof(bool)); // Check it because I don't remember how to use type bit here
tbl.Columns.Add("SectionOrderNumber", typeof(int));
tbl.Rows.Add(1,1,1,true,111);
tbl.Rows.Add(1,1,2,true,222);
cmd.Parameters.AddWithValue("@ProjectTemplateSectionDate", tbl);
cmd.ExecuteNonQuery();
