Use While Loop for updating data in Table in SQL Server

makumbi
 
on Jul 09, 2022 01:24 AM
516 Views

I would want to use a loop in SQL Server to get this code working as a stored procedure

Please help

Public Function Divisonstemp200()
    Try
        Using Con As New SqlConnection(conString)
            Con.Open()
            Using Com As New SqlCommand("Select * from temp", Con)
                Using RDR = Com.ExecuteReader()
                    If RDR.HasRows Then
                        Do While RDR.Read
                            admno = RDR.Item("admno").ToString()
                            ' If String.IsNullOrEmpty(RDR.Item("engrade")) Or String.IsNullOrEmpty(RDR.Item("engscore")) Then
                            If String.IsNullOrEmpty(If(IsDBNull(RDR.Item("engrade")), String.Empty, RDR.Item("engrade"))) Or String.IsNullOrEmpty(If(IsDBNull(RDR.Item("engscore")), String.Empty, RDR.Item("engscore"))) Then
 
                                Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc,Totalgrades = @sc2 WHERE admno = @admno")
                                cmd.Parameters.AddWithValue("@sc", "X")
                                cmd.Parameters.AddWithValue("@sc2", DBNull.Value)
                                cmd.Parameters.AddWithValue("@admno", admno)
                                ExecuteQuery(cmd, "UPDATE")
                            Else
                                If String.IsNullOrEmpty(If(IsDBNull(RDR.Item("sstscore")), String.Empty, RDR.Item("sstscore"))) Or String.IsNullOrEmpty(If(IsDBNull(RDR.Item("sstgrade")), String.Empty, RDR.Item("sstgrade"))) Then
                                    Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc,Totalgrades = @sc2 WHERE admno = @admno")
                                    cmd.Parameters.AddWithValue("@sc", "X")
                                    cmd.Parameters.AddWithValue("@sc2", DBNull.Value)
                                    cmd.Parameters.AddWithValue("@admno", admno)
                                    ExecuteQuery(cmd, "UPDATE")
                                Else
                                    If String.IsNullOrEmpty(If(IsDBNull(RDR.Item("sciencegrade")), String.Empty, RDR.Item("sciencegrade"))) Or String.IsNullOrEmpty(If(IsDBNull(RDR.Item("sciencescore")), String.Empty, RDR.Item("sciencescore"))) Then
                                        Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc,Totalgrades = @sc2 WHERE admno = @admno")
                                        cmd.Parameters.AddWithValue("@sc", "X")
                                        cmd.Parameters.AddWithValue("@sc2", DBNull.Value)
                                        cmd.Parameters.AddWithValue("@admno", admno)
                                        ExecuteQuery(cmd, "UPDATE")
                                    Else
                                        If String.IsNullOrEmpty(If(IsDBNull(RDR.Item("mathscore")), String.Empty, RDR.Item("mathscore"))) Or String.IsNullOrEmpty(If(IsDBNull(RDR.Item("mathsgrade")), String.Empty, RDR.Item("mathsgrade"))) Then
                                            Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc,Totalgrades = @sc2 WHERE admno = @admno")
                                            cmd.Parameters.AddWithValue("@sc", "X")
                                            cmd.Parameters.AddWithValue("@sc2", DBNull.Value)
                                            cmd.Parameters.AddWithValue("@admno", admno)
                                            ExecuteQuery(cmd, "UPDATE")
                                        Else
                                            If String.IsNullOrEmpty(If(IsDBNull(RDR.Item("totalgrades")), String.Empty, RDR.Item("totalgrades"))) Then
 
                                                Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc,Totalgrades = @sc2 WHERE admno = @admno")
                                                cmd.Parameters.AddWithValue("@sc", "X")
                                                cmd.Parameters.AddWithValue("@sc2", DBNull.Value)
                                                cmd.Parameters.AddWithValue("@admno", admno)
                                                ExecuteQuery(cmd, "UPDATE")
                                            End If
                                        End If
                                    End If
                                End If
                            End If
 
                            Select Case RDR.Item("totalgrades")
 
                                Case 4 To 12
                                    If RDR.Item("engrade") = 9 Or RDR.Item("mathsgrade") = 9 Or RDR.Item("sciencegrade") = 9 Or RDR.Item("sstgrade") = 9 Then
 
                                        Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc WHERE admno = @admno")
                                        cmd.Parameters.AddWithValue("@sc", "2")
                                        cmd.Parameters.AddWithValue("@admno", admno)
                                        ExecuteQuery(cmd, "UPDATE")
 
                                    Else
                                        Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc WHERE admno = @admno")
                                        cmd.Parameters.AddWithValue("@sc", "1")
                                        cmd.Parameters.AddWithValue("@admno", admno)
                                        ExecuteQuery(cmd, "UPDATE")
                                    End If 
 
                                Case 35 To 36
                                    Dim cmd As New SqlCommand("UPDATE temp SET Division = @sc WHERE admno = @admno")
                                    cmd.Parameters.AddWithValue("@sc", "U")
                                    cmd.Parameters.AddWithValue("@admno", admno)
                                    ExecuteQuery(cmd, "UPDATE")
                            End Select
                        Loop
                    End If
                End Using
            End Using
            Con.Close()
        End Using
    Catch ex As Exception
    End Try
    Return Nothing
End Function

Using your example i have come up with this but it updates only one record please help. Does not update other records

please help

PROCEDURE [dbo].[DIVISONUPDATE] AS
 
DECLARE @admno nvarchar(50)
DECLARE @engrade INT
DECLARE @engscore INT
DECLARE @sstscore INT
DECLARE @sstgrade INT
DECLARE @sciencegrade INT
DECLARE @sciencescore INT
DECLARE @mathscore INT
DECLARE @mathsgrade INT
DECLARE @Totalgrades INT 
DECLARE @Sum INT = 0
DECLARE @Counter INT, @TotalCount INT
  
SET @Counter = 1
   
SET @TotalCount = (SELECT COUNT(*) FROM temp)
    
WHILE (@Counter <= @TotalCount)
BEGIN
    SET @admno = (SELECT admno FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    SET @engrade = (SELECT engrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    SET @sstgrade = (SELECT sstgrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    SET @sciencegrade = (SELECT sciencegrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    SET @mathsgrade = (SELECT mathsgrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    SET @Totalgrades = (SELECT Totalgrades FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
 
    
    IF @Totalgrades <= 12
    BEGIN
    IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
    UPDATE temp SET Division = '2' WHERE admno = @admno
    else
       UPDATE temp SET Division = '1' WHERE admno = @admno
    END
    ELSE
    BEGIN
          
        IF @Totalgrades >=13 and @Totalgrades>=24
        BEGIN
           IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
    UPDATE temp SET Division = '3' WHERE admno = @admno
    else
       UPDATE temp SET Division = '2' WHERE admno = @admno
        END
        ELSE
         
         IF @Totalgrades >=25 and @Totalgrades>=28
        BEGIN
           IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
    UPDATE temp SET Division = '3' WHERE admno = @admno
    else
       UPDATE temp SET Division = '3' WHERE admno = @admno
        END
   ELSE
    IF @Totalgrades >=29 and @Totalgrades>=34
        BEGIN
           IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
    UPDATE temp SET Division = '4' WHERE admno = @admno
    else
       UPDATE temp SET Division = '4' WHERE admno = @admno
        END
        ELSE
 
         IF @Totalgrades >=35 and @Totalgrades>=36
        BEGIN
 
    UPDATE temp SET Division = 'U' WHERE admno = @admno
 
        END
    
    END
    SET @Counter = @Counter + 1
    CONTINUE;
END
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 11, 2022 02:33 AM
makumbi says:
IF @Totalgrades >=13 and @Totalgrades>=24

You are specifying wrong condition. It should be greater than 13 and less than 24.

Same for other condition as well.

Refer updated query.

CREATE PROCEDURE [dbo].[DIVISONUPDATE] 
AS 
BEGIN
	DECLARE @admno nvarchar(50)
	DECLARE @engrade INT
	DECLARE @engscore INT
	DECLARE @sstscore INT
	DECLARE @sstgrade INT
	DECLARE @sciencegrade INT
	DECLARE @sciencescore INT
	DECLARE @mathscore INT
	DECLARE @mathsgrade INT
	DECLARE @Totalgrades INT
 
	DECLARE @Sum INT = 0
	DECLARE @Counter INT, @TotalCount INT
  
	SET @Counter = 1   
	SET @TotalCount = (SELECT COUNT(*) FROM temp)    
	WHILE (@Counter <= @TotalCount)
	BEGIN
		SET @admno = (SELECT admno FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
		SET @engrade = (SELECT engrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
		SET @sstgrade = (SELECT sstgrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
		SET @sciencegrade = (SELECT sciencegrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
		SET @mathsgrade = (SELECT mathsgrade FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
		SET @Totalgrades = (SELECT Totalgrades FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
    
		IF @Totalgrades <= 12
		BEGIN
			IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
				UPDATE temp SET Division = '2' WHERE admno = @admno
			ELSE
				UPDATE temp SET Division = '1' WHERE admno = @admno
		END
		ELSE IF @Totalgrades >=13 and @Totalgrades<=24
		BEGIN
			IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
				UPDATE temp SET Division = '3' WHERE admno = @admno
			ELSE
				UPDATE temp SET Division = '2' WHERE admno = @admno
		END
		ELSE IF @Totalgrades >=25 and @Totalgrades<=28
		BEGIN
			IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
				UPDATE temp SET Division = '3' WHERE admno = @admno
			ELSE
				UPDATE temp SET Division = '3' WHERE admno = @admno
		END
		ELSE IF @Totalgrades >=29 and @Totalgrades<=34
		BEGIN
			IF @engrade='9' Or @sstgrade='9' Or @sciencegrade='9' Or @mathsgrade='9'
				UPDATE temp SET Division = '4' WHERE admno = @admno
			ELSE
				UPDATE temp SET Division = '4' WHERE admno = @admno
			END
		ELSE IF @Totalgrades >=35 and @Totalgrades<=36
		BEGIN 
			UPDATE temp SET Division = 'U' WHERE admno = @admno 
		END
		SET @Counter = @Counter + 1
		CONTINUE;
	END
END