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
