UPDATE: i want to Put a Validity-Check so that Multiple update doesn''t Occur
-
Hi friends ! HI Sushila, thanx for ur Help !!
The Update command that i am using in following StoredProcedure below WORKS fine ; Data gets updated properly BUT every next time the Same storedproc Executes [even if by Mistake] for the same parameters; Then I cant Stop the Multiple-Update from Occuring;Note that There are Multiple Instance of the Update-command BUT all of 'em r having Similar Logic.
Note that There are Multiple Instance of the Update-command BUT all of 'em r having Similar Logic.I Ultimately want to Prevent each of these Update-Actions from Running MORE than Once [the very First Time].
Currently I am using of Temporary variables in the Procedure BUT so far i haven't been Able to CRACK it ...........
So Can any1 here [Sushila] Pls. Tell me how to Provide a Check to Prevent Multiple Run of the Update Commands.
-- sp_AptitudeReport 2,1,8,'A',36
ALTER PROCEDURE sp_AptitudeReport
@iYear int,
@iSchoolCode int,
@iClass int,
@strDivision char(1),
@StdRollNo int
As
Set Nocount on
Declare @ecount as int
Declare @mCount as int
Declare @sCount as int
Declare @compCount as int
SET @ecount=0
SET @mCount=0
SET @sCount=0
SET @compCount=0
Insert Into Temp_Aptitude_Result(StudentNo,StudentRollNo,Class,Division,AcademicYearCode)
Select A.Student_No,A.Student_RollNo,A.Student_Class,A.Student_Division,A.AcademicYear_Code
From trn_studentclass A
--inner JOIN mst_student B ON A.Student_No=B.Student_No
--inner JOIN mst_house C ON B.Student_House=C.House_Code
--inner JOIN mst_school D ON A.School_Code=D.School_Code
--inner JOIN trn_Progress_Card_Data E On A.Student_No=E.Student_No
Where A.School_Code =@iSchoolCode
And A.AcademicYear_Code=@iYear
And A.Student_Class =@iClass
And A.Student_Division =@strDivision
And A.Student_RollNo =@StdRollNo
While(<>@eCount)
BEGIN
UPDATE Temp_Aptitude_Result
SET EnglishMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='English' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo AND Ctr =@eCount),
TermType=2,SubjectName='English' --,Class=8,Division='A'
SET @ecount = (
SELECT Ctr from trn_Progress_Card_Data WHERE Subject_Name='English' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo )
END
While(@mCount<>0)
BEGIN
UPDATE Temp_Aptitude_Result
SET MathsMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Mathematics' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo AND Ctr =@mCount),
TermType=2,SubjectName='Mathematics' --,Class=8,Division='A'
SET @mCount = (
SELECT Ctr from trn_Progress_Card_Data WHERE Subject_Name='Mathematics' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo )
END
While(@sCount<>0)
BEGIN
UPDATE Temp_Aptitude_Result
SET ScienceMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Science' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo AND Ctr =@sCount),
TermType=2,SubjectName='Science' --,Class=8,Division='A'
SET @sCount = (
SELECT Ctr from trn_Progress_Card_Data WHERE Subject_Name='Science' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo )
END
While(@compCount<>0)
BEGIN
UPDATE Temp_Aptitude_Result
SET ComputerMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Computers' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo AND Ctr =@compCount),
TermType=2,SubjectName='Computers' --,Class=8,Division='A'
SET @compCount = (
SELECT Ctr from trn_Progress_Card_Data WHERE Subject_Name='Computers' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo )
End
-- SELECT * FROM trn_Progress_Card_Data WHERE Subject_Name='computers' AND Student_Std=8 AND Student_Division='A' AND Term_Type=2
SELECT StudentRollNo,EnglishMarks,MathsMarks,ScienceMarks,ComputerMarks FROM Temp_Aptitude_Result WHERE TermType=2 AND Class=@iClass AND ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo --WHERE TermType=2
Set Nocount Off
--------------------------------------------------------------------------------
__________________
Thanx & Regards,
Iyer Sankara S H [Kartik]
Software Engineer,
ParaMatrix Technologies,
client: Kotak Securities,
Mumbai.
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services -
Hey SQL ppl !
Now i am using the Following StoredProc-Code ; Can any1 Suggest a Better/Compact method of Doing the Above Task ...
[ TASK = The Update command that i am using in following StoredProcedure below WORKS fine ; Data gets updated properly BUT every next time the Same storedproc Executes [even if by Mistake] for the same parameters; Then I cant Stop the Multiple-Update from Occuring;
Note that There are Multiple Instance of the Update-command BUT all of 'em r having Similar Logic.
Currently I am using of Temporary variables in the Procedure BUT so far i haven't been Able to CRACK it ...........
Pls. Tell me how to Provide a Check to Prevent Multiple Run of the Update Commands.
PLEASE NOTE that :-
The Update command that i am using in following StoredProcedure below WORKS fine ; Data gets updated properly BUT every next time the Same storedproc Executes [even if by Mistake] for the same parameters; Then I cant Stop the Multiple-Update from Occuring;Note that There are Multiple Instance of the Update-command BUT all of 'em r having Similar Logic.
Note that There are Multiple Instance of the Update-command BUT all of 'em r having Similar Logic.I Ultimately want to Prevent each of these Update-Actions from Running MORE than Once [the very First Time].
]
c o d e :-
ALTER PROCEDURE sp_AptitudeReport
@iYear int,
@iSchoolCode int,
@iClass int,
@strDivision char(1),
@StdRollNo int
-- Must Include another Param FOR Marks of the Aptitude Test ...
As
Set Nocount on
Declare @ecount as int
Declare @mCount as int
Declare @sCount as int
Declare @compCount as int
Insert Into Temp_Aptitude_Result(StudentNo,StudentRollNo,Class,Division,AcademicYearCode,TermType,SubjectName)
Select A.Student_No,A.Student_RollNo,A.Student_Class,A.Student_Division,A.AcademicYear_Code,E.Term_Type,E.Subject_Name
From trn_studentclass A
inner JOIN trn_Progress_Card_Data E On A.Student_No=E.Student_No
-- @@Identity
Where A.School_Code =@iSchoolCode
And A.AcademicYear_Code=@iYear
And A.Student_Class =@iClass
And A.Student_Division =@strDivision
And A.Student_RollNo =@StdRollNo
SET @ecount= (SELECT eCount from Temp_Aptitude_Result WHERE SubjectName='English' and TermType=2
AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)While (@eCount=1)
BEGIN
UPDATE Temp_Aptitude_Result
SET EnglishMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='English' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='English' --,Class=8,Division='A'
UPDATE Temp_Aptitude_Result
SET eCount = 0
END
SET @mCount= (SELECT mCount from Temp_Aptitude_Result WHERE SubjectName='Mathematics' and TermType=2
AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)While (@mCount=1)
BEGIN
UPDATE Temp_Aptitude_Result
SET MathsMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Mathematics' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Mathematics' --,Class=8,Division='A'
UPDATE Temp_Aptitude_Result
SET mCount = 0
END
SET @sCount= (SELECT sCount from Temp_Aptitude_Result WHERE SubjectName='Science' and TermType=2
AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)While (@sCount=1)
BEGIN
UPDATE Temp_Aptitude_Result
SET ScienceMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Science' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Science' --,Class=8,Division='A'
UPDATE Temp_Aptitude_Result
SET sCount = 0
END
SET @compCount= (SELECT compCount from Temp_Aptitude_Result WHERE SubjectName='Computers' and TermType=2
AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)While (@compCount=1)
BEGIN
UPDATE Temp_Aptitude_Result
SET ComputerMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Computers' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Computers' --,Class=8,Division='A'
UPDATE Temp_Aptitude_Result
SET compCount = 0
End
-- SELECT * FROM trn_Progress_Card_Data WHERE Subject_Name='computers' AND Student_Std=8 AND Student_Division='A' AND Term_Type=2
SELECT StudentRollNo,EnglishMarks,MathsMarks,ScienceMarks,ComputerMarks FROM Temp_Aptitude_Result --WHERE TermType=2 AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo --WHERE TermType=2 Set Nocount Off
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services -
This is Latest StoredProc after I made it a little more Compact BUT the problem/Task remains !!!
I want to Prevent Update command from executing Multiple times .....
-- SELECT * FROM MST_SUBJECT where Subject_Name LIKE 'computers'
--select * from trn_progress_card_data ORDER BY Ctr where student_std = 8 and subject_name='English' and student_division='A'
-- sp_AptitudeReport 1,1,8,'A',5
ALTER PROCEDURE sp_AptitudeReport
@iYear int,
@iSchoolCode int,
@iClass int,
@strDivision char(1),
@StdRollNo int
-- Must Include another Param FOR Marks of the Aptitude Test ...
As
Set Nocount on
Declare @ecount as int
Declare @mCount as int
Declare @sCount as int
Declare @compCount as int
Insert Into Temp_Aptitude_Result(StudentNo,StudentRollNo,Class,Division,AcademicYearCode,TermType,SubjectName)
Select A.Student_No,A.Student_RollNo,A.Student_Class,A.Student_Division,A.AcademicYear_Code,E.Term_Type,E.Subject_Name
From trn_studentclass A
inner JOIN trn_Progress_Card_Data E On A.Student_No=E.Student_No
Where A.School_Code =@iSchoolCode
And A.AcademicYear_Code=@iYear
And A.Student_Class =@iClass
And A.Student_Division =@strDivision
And A.Student_RollNo =@StdRollNo
SET @ecount= (SELECT eCount from Temp_Aptitude_Result WHERE SubjectName='English' and TermType=2
AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)While (@eCount=1)
BEGIN
UPDATE Temp_Aptitude_Result
SET EnglishMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='English' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='English', eCount = 0
UPDATE Temp_Aptitude_Result
SET MathsMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Mathematics' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Mathematics' , mCount = 0
UPDATE Temp_Aptitude_Result
SET ScienceMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Science' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Science' , sCount = 0
UPDATE Temp_Aptitude_Result
SET ComputerMarks =(SELECT TTMarksObtained from trn_Progress_Card_Data WHERE Subject_Name='Computers' and Term_Type=2
AND Student_Std=@iClass AND Student_Division=@strDivision AND Student_RollNo=@StdRollNo),
TermType=2,SubjectName='Computers' , compCount = 0
END
--SET @mCount= (SELECT mCount from Temp_Aptitude_Result WHERE SubjectName='Mathematics' and TermType=2
-- AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)--SET @sCount= (SELECT sCount from Temp_Aptitude_Result WHERE SubjectName='Science' and TermType=2
-- AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)--SET @compCount= (SELECT compCount from Temp_Aptitude_Result WHERE SubjectName='Computers' and TermType=2
-- AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo)SELECT StudentRollNo,EnglishMarks,MathsMarks,ScienceMarks,ComputerMarks FROM Temp_Aptitude_Result WHERE TermType=2 AND Class=@iClass AND <A href="mailto
ivision=@strDivision">Division=@strDivision AND StudentRollNo=@StdRollNo --WHERE TermType=2 Set Nocount Off
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services -
after each update statement, check the @@ROWCOUNT value...it tells you the number of rows affected by the previous transaction.
with that value, you can logically decide what to do next...wheter it is continue or rollback a transaction.
use northwind
begin tran
select * from employees
update Employees set lastname='whatever'
if @@rowcount > 1 --this will return 9 rows affected
begin
RAISERROR ('Oops affected more than one row.', 16, 1)
select * from employees
rollback tran
select * from employees
end
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
@ Lowell : Thanx for ur Help !!!
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply