I tried that and it does not work either.
I think that the cause must be another thing.
I'm posting the SP code down here to see if you can
help me.
I'm stumped with this.
The problem seems being that the second cursor for
some reason is not working.
The parts commented were already tested without any
relief.
Thanks for your help .
regards
Mario
CREATE proc dbo.InfoEkSelfASS1
as
begin
/*Declring local variables*/
DECLARE
@UsernameAux VARCHAR(255),
@FirstNameAux VARCHAR(255),
@LastNameAux VARCHAR(255),
@TitleAux VARCHAR(255),
@TutorialTitleAux VARCHAR(255),
@QuestionNumberAux INT,
@StudentAnsweredCorrectlyAux VARCHAR(255) ,
@StudentAnsweredCorrectlyAux1 INT,
@AssessmentStartTimeAux DATETIME,
@CompletedAux VARCHAR(255),
@CompletedAux1 INT,
@DurationAux INT,
@FistAccessAux DATETIME,
@LastAccessAux DATETIME,
@Count INT
/* deleting the previous table(Final result) */
DELETE FROM ElementkSelfSRpt
/* Creating one cursor for the table assessment*/
DECLARE assessm_cur CURSOR FOR
SELECT
Username, FirstName,
LastName,Title,Tutorial,QNumber,StudentAswCorrect ,
AssTime
FROM
EkSelfAssRpts
FOR READ ONLY
OPEN assessm_cur
FETCH assessm_cur INTO
@UsernameAux, @FirstNameAux, @LastNameAux, @TitleAux,
@TutorialTitleAux, @QuestionNumberAux,
@StudentAnsweredCorrectlyAux1, @AssessmentStartTimeAux
WHILE @@FETCH_STATUS =0
begin
EXEC InfoEkSelfDuration @UserName=@UsernameAux,
@Title=@TitleAux, @Duration= @DurationAux output
EXEC InfoEkSelfLT @UserName=@UsernameAux,
@Title=@TitleAux, @LastAccess= @LastAccessAux output
EXEC InfoEkSelfST @UserName=@UsernameAux,
@Title=@TitleAux, @FistAccess = @FistAccessAux output
EXEC InfoEkSelfComp @UserName=@UsernameAux,
@Title=@TitleAux, @Completed= @CompletedAux1 output
IF @CompletedAux1 =1
SELECT @CompletedAux="Yes"
ELSE
SELECT @CompletedAux="No"
IF @StudentAnsweredCorrectlyAux1 =1
SELECT @StudentAnsweredCorrectlyAux = "Yes"
ELSE
SELECT @StudentAnsweredCorrectlyAux="No"
INSERT ElementkSelfSRpt (Username, [First
Name],[Last Name],Title , [Tutorial Title], [Question
Number] , [Student Answered Correctly],[Assessment
Start Time], Completed , Duration ,
FistAccess,LastAccess )
VALUES(@UsernameAux, @FirstNameAux ,@LastNameAux,
@TitleAux , @TutorialTitleAux , @QuestionNumberAux ,
@StudentAnsweredCorrectlyAux , @AssessmentStartTimeAux
, @CompletedAux, @DurationAux, @FistAccessAux,
@LastAccessAux )
FETCH assessm_cur INTO
@UsernameAux, @FirstNameAux, @LastNameAux ,
@TitleAux, @TutorialTitleAux, @QuestionNumberAux,
@StudentAnsweredCorrectlyAux1, @AssessmentStartTimeAux
end
CLOSE assessm_cur
DEALLOCATE assessm_cur
/*This is in case there is no data in EkSelfAssRpts
and there is in EkSelfTutorialRpts*/
/* Creating one cursor for the table assessment*/
DECLARE assessm_curt CURSOR FOR
SELECT
Username, FirstName, LastName,Title,TutorialTitle
FROM
EkSelfTutorialRpts
FOR READ ONLY
OPEN assessm_curt
FETCH assessm_curt INTO
@UsernameAux, @FirstNameAux, @LastNameAux, @TitleAux,
@TutorialTitleAux
WHILE @@FETCH_STATUS =0
begin
/*SELECT @Count=COUNT(UserName) FROM EkSelfAssRpts
WHERE UserName=@UsernameAux AND
FirstName=@FirstNameAux AND Title=@TitleAux*/
/*IF NOT EXISTS ( SELECT DISTINCT UserName FROM
EkSelfAssRpts
WHERE UserName=@UsernameAux AND
FirstName=@FirstNameAux AND Title=@TitleAux)
BEGIN*/
/*If I 'm here is because I found one without
assessment*/
EXEC InfoEkSelfDuration
@UserName=@UsernameAux, @Title=@TitleAux, @Duration=
@DurationAux output
EXEC InfoEkSelfLT @UserName=@UsernameAux,
@Title=@TitleAux, @LastAccess= @LastAccessAux output
EXEC InfoEkSelfST @UserName=@UsernameAux,
@Title=@TitleAux, @FistAccess = @FistAccessAux output
EXEC InfoEkSelfComp @UserName=@UsernameAux,
@Title=@TitleAux, @Completed= @CompletedAux1 output
IF @CompletedAux1 =1
SELECT @CompletedAux="Yes"
ELSE
SELECT @CompletedAux="No"
SELECT @AssessmentStartTimeAux=null
SELECT @QuestionNumberAux=0
SELECT @StudentAnsweredCorrectlyAux="No"
INSERT ElementkSelfSRpt (Username, [First
Name],[Last Name],Title , [Tutorial Title], [Question
Number] , [Student Answered Correctly],[Assessment
Start Time], Completed , Duration ,
FistAccess,LastAccess )
VALUES(@UsernameAux, @FirstNameAux
,@LastNameAux, @TitleAux , @TutorialTitleAux ,
@QuestionNumberAux , @StudentAnsweredCorrectlyAux ,
@AssessmentStartTimeAux , @CompletedAux, @DurationAux,
@FistAccessAux, @LastAccessAux )
/*end */
FETCH assessm_curt INTO
@UsernameAux, @FirstNameAux, @LastNameAux,
@TitleAux, @TutorialTitleAux
end
CLOSE assessm_curt
DEALLOCATE assessm_curt
end
GO
/////////////////
--- [EMAIL PROTECTED] escribi�: > from what i
have been told, this is suppose to be
> faster and more efficient.
> i just like the fact that i don't have to declare
> variables. ;)
>
> not only that, you don't have to perform error
> checking.
>
>
> IF NOT EXISTS(
> SELECT UserName
> FROM EkSelfAssRpts
> WHERE UserName=@UsernameAux AND
> FirstName=@FirstNameAux AND Title=@TitleAux
> )
> begin
>
> your TSQL code
>
> end
>
>
> Anthony Petruzzi
> Webmaster
> 954-321-4703
> [EMAIL PROTECTED]
> http://www.sheriff.org
>
_________________________________________________________
Do You Yahoo!?
Informaci�n de Estados Unidos y Am�rica Latina, en Yahoo! Noticias.
Vis�tanos en http://noticias.espanol.yahoo.com
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
