Manage a for loop in sql related to the change in c#? there are more than 4 modifier that we need to add to our list

Question

I have C# and SQL code that work together. Issue was I need to add more than 4 modifier in my list but it was not possible, so I change the c# code and related to that I need to change my SQL code. I do not know how may I put for loop in SQL. Help me please.

I have a table that I need to add "n" modifier on it.

Here is the c# code that I changed:

int n = updatedStudy.Modifiers.Count;

for (int i = 1; i < n; i++)
{
    string @pu_examCodeModifier = "@pu_examCodeModifier" + i;
    sqlParm.Add(@pu_examCodeModifier, updatedStudy.Modifiers[i - 1].Key, SQLEnumerations.SQLDataTypes.UNIQUEIDENTIFIER, 0);
}

and here is SQL code that I need to change :

CREATE PROCEDURE [v0000].[apUpdateOrder] (
    @pu_fillerOrderKey UNIQUEIDENTIFIER,
    @pu_examCodeKey UNIQUEIDENTIFIER,
    @pu_examCodeModifier1 UNIQUEIDENTIFIER = NULL ,
    @pu_examCodeModifier2 UNIQUEIDENTIFIER = NULL,
    @pu_examCodeModifier3 UNIQUEIDENTIFIER = NULL,
    @pu_examCodeModifier4 UNIQUEIDENTIFIER = NULL,
    @ps_fillerOrderStatusCd VARCHAR(1),
    @ps_priorityCd VARCHAR(10),
    @pb_exam_stat_flag BIT)
AS
BEGIN
    --exam code modifiers
    IF (@pb_updateStudyModifiers IS NOT NULL)
    BEGIN
        DELETE FROM dbo.mapProcedureStepExamCodeModifier 
        WHERE procedureStepKey = @lu_procedureStepKey

        IF (@pu_examCodeModifier1 IS NOT NULL)
           INSERT INTO dbo.mapProcedureStepExamCodeModifier (procedureStepKey, examCodeModifierKey)
           VALUES (@lu_procedureStepKey, @pu_examCodeModifier1)

        IF (@pu_examCodeModifier2 IS NOT NULL)
           INSERT INTO dbo.mapProcedureStepExamCodeModifier (procedureStepKey, examCodeModifierKey)
           VALUES (@lu_procedureStepKey, @pu_examCodeModifier2)

        IF (@pu_examCodeModifier3 IS NOT NULL)
           INSERT INTO dbo.mapProcedureStepExamCodeModifier (procedureStepKey, examCodeModifierKey)
           VALUES (@lu_procedureStepKey, @pu_examCodeModifier3)

        IF (@pu_examCodeModifier4 IS NOT NULL)
           INSERT INTO dbo.mapProcedureStepExamCodeModifier (procedureStepKey, examCodeModifierKey)
           VALUES (@lu_procedureStepKey, @pu_examCodeModifier4)   
   END

Answer 1

Putting loops onto the DB side is generally adviseable. Putting this kind of work onto the Client Side is only slower and prone to race conditions.

As I read it, you want to react on the previous values of that row when deleting it. There are several ways to go about this:

Delete Trigger. Especially if this is more then a one time re-stucturing and the table will stay around, I would go for DML triggers. It is still part of the Original DML Orders Transaction and Lockout, so it is has little danger of race conditions.

OUTPUT Syntax. There is often a need to get data from rows you just inserted, Updated or Deleted. The OUTPUT clause is there for you. Once again, this is part of the Original DML's Transaction. Plus it can access auto values that were literally just created (like Primary Keys).

The core Loop of SQL is the more generic while. The trick is to make a Stored Procedure that takes a Table Valued Paramter basically an array, where you hand in stuff like Primary. Or extracts the Primary keys into a local variable itself.

If you need a index or any other temporary variable, just make one yourself. That is what DECLARE is there for.

One thing about loop modification worth considering is the transactions and lockouts. By default every single DML statemetn will be wrapped into a seperate Transaction and aquire seperate lockout. That can add a ton of overhead. So it can be beneficial to do the transaction and lock creation yourself - just keep in mind to lock all tables you may need instantly.

Answer 2

Regarding the example you have provided, there are a couple of problems in there, like where @pb_updateStudyModifiers and @lu_procedureStepKey come from.

Regarding the specific question of how to loop in SQL, the answer is:

-If you already know the values, and they are numeric, use a while:

DECLARE @ID INT = 0

WHILE @ID < 3
BEGIN
    SELECT @ID = @ID + 1
    SELECT 'The current ID is: ' + CAST(@ID AS VARCHAR)
END

-If you need to loop through other value types, you will need an auxiliary table and a CURSOR. This is an example of how to loop through a table ( doing the same thing I did above using just the INT variable ):

DECLARE @EXAMPLE_IDS TABLE (
    ID INT
)
-- POPULATE THE EXAMPLE TABLE
INSERT INTO @EXAMPLE_IDS VALUES ( 1 )
INSERT INTO @EXAMPLE_IDS VALUES ( 2 )
INSERT INTO @EXAMPLE_IDS VALUES ( 3 )


DECLARE EXAMPLE_CURSOR CURSOR FOR SELECT ID FROM @EXAMPLE_IDS

DECLARE @CURRENT_ID INT

OPEN EXAMPLE_CURSOR

-- GET FIRST ELEMENT
FETCH NEXT FROM EXAMPLE_CURSOR INTO @CURRENT_ID

-- THIS IS YOUR LOOP
WHILE @@FETCH_STATUS = 0
BEGIN
    -- DO YOUR STUFF HERE 
    SELECT 'The current ID is: ' + CAST(@CURRENT_ID AS VARCHAR)
    -- GET NEXT ID
    FETCH NEXT FROM EXAMPLE_CURSOR INTO @CURRENT_ID
END

CLOSE EXAMPLE_CURSOR
DEALLOCATE EXAMPLE_CURSOR