- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 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
DECLARE @type tinyint
SET @type = Case
when @WtpID = '2CADA6AF-C99C-44DE-BE67-5361CF6E0785' then 0
when @wtpID = 'AFD4A776-6714-409B-AA79-D570FF456C02' then 1
when @wtpID = 'BE2804C1-BDB0-4B03-B3C0-77104F6203DD' then 2
when @wtpID in ('8687AFE1-8049-4440-8E24-4FC639402313',
'BDE881B9-7C7C-40DF-BC19-EA85D3F5F15E',
'56143A9A-B151-41BA-8413-D5934FD4CA1B') then 3 + Case when @IsExam=1 then 1 else 0 end
when @wtpID in ('D24F7A71-AC73-4596-95A6-8BE6CE35A2FB',
'CA29E728-44E9-4844-8712-C4024876D6C6')
then 5 + Case when @IsExam=1 then 1 else 0 end
end
print @type
SET @codeMark = [dbo].[GetCodeByMark](@type, @Mark, 3)
SET @SresPresent = Case when @Mark in ('2','3','4','5') then 1 else 0 end
DECLARE @r TABLE(ScaID uniqueidentifier, PhfRN tinyint)
INSERT INTO @r(ScaID, PhfRN)
SELECT MIN(Cast(ScaID as varchar(50))), PhfRN
FROM STUDControlActions
join PhFormsControl on PhfID = ScaPhfID
WHERE ScaDbgID = @DbgID and ScaWtpID = '56E43643-D0B6-45C6-B53B-09C1149D3671' and PhfAttR=1
GROUP BY PhfRN
-- select * from @r
DECLARE @rCount tinyint = (SELECT COUNT(PhfRN) FROM @r)
DECLARE @rn tinyint = 1
print 'np:'
print @np
DECLARE @cmd varchar(500)=''
IF @np=0
BEGIN
if @TemplateType = 3
BEGIN
SET @itogBall = Case when @itogBallT is null then null when ISNUMERIC(@itogBallT)=1 then CAST(@itogBallT as INT)
when @itogBallT in ('*','x','х') then -9 when @itogBallT = 'z' then -13 else null end
SET @SresMark = null
IF @codeMark < 0 SET @SresItogMark = @codeMark
ELSE SET @SresItogMark = @itogBall
SET @cmd = '[stud].[STUD_ResultsAdd] @SresID = ' + Cast(@SresID as varchar(50)) + ', '+
'@ScaID = '+Cast(@ScaID as varchar(50))+', @StudNom = ' + @StudNom + ','+
'@SresPresent = ' + IsNull(Cast(@SresPresent as varchar(10)),'null') + ', @SresMark = ' + IsNull(Cast(@SresMark as varchar(10)),'null')+ ',' +
'@SresTheme = null, @SresComment = null, @SID = ' + Cast(@SID as varchar(10)) + ', @vEditor = 1, @SresItogMark = ' + Cast(@SresItogMark as varchar(10)) +
', @SresSresID = null, @sNID = null'
print @cmd
EXEC [stud].[STUD_ResultsAdd] @SresID = @SresID, @ScaID = @ScaID, @StudNom = @StudNom,
@SresPresent = @SresPresent, @SresMark = @SresMark,
@SresTheme = null, @SresComment = null,
@SID = @SID, @vEditor = 1, @SresItogMark = @SresItogMark, @SresSresID = null, @sNID = null
IF @type in (0,1)
BEGIN
SET @SresMark = Case when @codeMark < 0 then @codeMark else @itogBall end
SET @SresItogMark = Case
when @codeMark < 0 then @codeMark
when IsNull(@itogBall,-1)>=60 then 1
when IsNull(@itogBall,-1)>=0 and IsNull(@itogBall,-1)<60 then 0
else null end
SET @SresPresent = Case when IsNull(@itogBall,-1)>=0 then 1 else 0 end
SELECT @ScaID = null, @SresID = null, @WtpID = 'BE2804C1-BDB0-4B03-B3C0-77104F6203DD'
SELECT @ScaID = ScaID FROM STUDControlActions WHERE ScaDbgID = @DbgID and ScaWtpID = @WtpID
IF @ScaID is null RAISERROR('Запись об аттестации разделов отсутствует!', 16, 1);
SELECT @SresID = SresID FROM STUDResults WHERE SresScaID = @ScaID and SresSID = @SID
SET @cmd = '[stud].[STUD_ResultsAdd] @SresID = ' + Cast(@SresID as varchar(50)) + ', '+
'@ScaID = '+Cast(@ScaID as varchar(50))+', @StudNom = ' + @StudNom + ','+
'@SresPresent = ' + Cast(@SresPresent as varchar(10)) + ', @SresMark = ' + Cast(@SresMark as varchar(10))+ ',' +
'@SresTheme = null, @SresComment = null, @SID = ' + Cast(@SID as varchar(10)) + ', @vEditor = 1, @SresItogMark = ' + Cast(@SresItogMark as varchar(10)) +
', @SresSresID = null, @sNID = null'
print @cmd
EXEC [stud].[STUD_ResultsAdd] @SresID = @SresID, @ScaID = @ScaID, @StudNom = @StudNom,
@SresPresent = @SresPresent, @SresMark = @SresMark,
@SresTheme = null, @SresComment = null,
@SID = @SID, @vEditor = 1, @SresItogMark = @SresItogMark, @SresSresID = null, @sNID = null
SET @SresMark = Case when @codeMark = -9 then -9
when @codeMark in (-12,-13) then 0
when IsNull(@itogBall,-1)>=0 then @itogBall
else null end
SET @SresItogMark = null
SET @SresPresent = Case when IsNull(@itogBall,-1)>=0 then 1 else 0 end
SELECT @ScaID = null, @SresID = null, @WtpID = '56E43643-D0B6-45C6-B53B-09C1149D3671'
SELECT @ScaID = ScaID FROM STUDControlActions
join PhFormsControl on PhfID = ScaPhfID
WHERE ScaDbgID = @DbgID and ScaWtpID = @WtpID and PhfAttR=1 and PhfRN=1
IF @ScaID is null RAISERROR('Запись для 1 раздела отсутствует!', 16, 1);
SELECT @SresID = SresID FROM STUDResults WHERE SresScaID = @ScaID and SresSID = @SID
Показательный фрагмент из хранимой процедуры (общей длиной > 500 строк) MS SQL Server 2008 с боевой системы, которую приходится поддерживать. Хранимок в подобном стиле в проекте - несколько сотен. Немногочисленные комментарии выпилены, т.к. ими можно пренебречь.
Удивительно, но пользователи постоянно жалуются, что что-то не работает как надо...