usp_SaveLabExerciseAttempts.sql
3.37 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[usp_SaveLabExerciseAttempts] Script Date: 03/05/2018 12:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author: Nikita Kulshreshtha
-- Create date: 21-Feb-2018
-- Description: To insert a LabExerciseData
-- ====================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_SaveLabExerciseAttempts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_SaveLabExerciseAttempts]
GO
CREATE PROCEDURE [dbo].[usp_SaveLabExerciseAttempts]
-- Add the parameters for the stored procedure here
@UserId int,
@LabExerciseIdentifier nchar(10),
@LastQuestion int,
@TotalQuestions int,
@LabExerciseTempTbl LabExerciseTempTableType READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @labQuizId int;
DECLARE @count int;
DECLARE @Score int;
DECLARE @MaxScore int;
DECLARE @UserAnswers nvarchar(2000);
DECLARE @QuestionNo int;
DECLARE @CorrectAnswers nvarchar(2000);
DECLARE @DragItems nvarchar(max)
DECLARE @c int
INSERT INTO LabExercise(UserId, LabExerciseIdentifier,IsActive, LastQuestion,TotalQuestions,SavedDate, ModifiedDate,CallFrom)
values(@UserId, @LabExerciseIdentifier, 1, @LastQuestion, @TotalQuestions,GETDATE(),GETDATE(),'AIAHTML5')
print 'inserted in 1st table'
SELECT @labQuizId = max(Id) from LabExercise where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId
print @labQuizId
-- --check count before update
SELECT @count = count(*) FROM LabExercise where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId and Id!=@labQuizId
print @count
if(@count>0)
BEGIN
print N'inside if'
UPDATE LabExercise set IsActive = 0 where LabExerciseIdentifier = @LabExerciseIdentifier and UserId= @UserId and Id!=@labQuizId
----create temp table
create table #Temp
(
MaxScore int,
UserAnswers NVarchar(2000),
QuestionNo int,
CorrectAnswers NVarchar(2000),
DragItems NVarchar(2000),
Score int
)
select * from @LabExerciseTempTbl
insert into #Temp(MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems,Score)
select MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems,Score from @LabExerciseTempTbl
select @c = count(*) from #Temp
print @c
print 'inserted in temp table'
DECLARE LABEX_CURSOR CURSOR
LOCAL FORWARD_ONLY FOR
SELECT MaxScore, UserAnswers,QuestionNo, CorrectAnswers, DragItems,Score FROM #Temp
print 'cursor declared'
OPEN LABEX_CURSOR
print 'cursor open'
FETCH NEXT FROM LABEX_CURSOR INTO @MaxScore, @UserAnswers, @QuestionNo, @CorrectAnswers, @DragItems,@Score
WHILE @@FETCH_STATUS = 0
BEGIN
print 'inside cursor'
INSERT INTO LabExerciseDetails(LabQuizId,StateObject,Score,MaxScore,UserAnswers,QuestionNo,CorrectAnswers,DragItems) values(@labQuizId,null,@Score,@MaxScore,@UserAnswers,@QuestionNo,@CorrectAnswers,@DragItems)
FETCH NEXT FROM LABEX_CURSOR INTO @MaxScore, @UserAnswers, @QuestionNo, @CorrectAnswers, @DragItems,@Score
print 'end cursor'
END
CLOSE LABEX_CURSOR
DEALLOCATE LABEX_CURSOR
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END