create table alloc_test (
UID char(6) not null ,
[EMP ID] INT not null foreign key references ASSOCIATES([EMP ID]),
[Stage ID] varchar(10) not null foreign key references [Stage Description]([Stage ID]),
[Date of Assign] date not null default (getdate()),
primary key ([uid],[emp id])
)
alter PROC ALLOC_1
@UID CHAR(6),
@EMP_ID INT,
@STAGE_ID varchar(10),
@DATE_OF_ASSIGN DATE
AS
BEGIN
declare
@EMPUID int, @IDEN int,@PREP int
set @EMPUID = (select count() from alloc_test where uid = @UID and [EMP ID] = @EMP_ID)
set @IDEN = (select count() from alloc_test where uid = @UID and @STAGE_ID = 'IDEN')
set @PREP = (select count(*) from alloc_test where uid = @UID and @STAGE_ID = 'PREP')
IF EXISTS(SELECT 'true' FROM alloc_test WHERE UID=@UID AND [Stage ID]=@STAGE_ID)
BEGIN
PRINT'STAGE ALREADY ASSIGNED'
END
ELSE
IF @EMPUID = 0 and @IDEN = 0 and @STAGE_ID = 'Iden'
begin
INSERT INTO alloc_test([UID],[EMP ID],[Stage ID],[DATE OF ASSIGN]) values(@UID,@EMP_ID,@STAGE_ID,@DATE_OF_ASSIGN)
PRINT 'Identification Assign Success'
end
ELSE
If @EMPUID = 0 and @IDEN = 0 and @STAGE_ID in ('prep','vali')
begin
print 'Please Assign Identification'
end
else
IF @EMPUID= 0 AND @IDEN = 1 AND @STAGE_ID = 'PREP'
begin
INSERT INTO alloc_test([UID],[EMP ID],[Stage ID],[DATE OF ASSIGN]) values(@UID,@EMP_ID,@STAGE_ID,@DATE_OF_ASSIGN)
PRINT 'Preparation Assign Success'
end
else
If @EMPUID=0 AND @IDEN = 1 and @PREP=0 AND @STAGE_ID = 'VALID'
begin
print 'Please Assign Preparation '
end
END
exec ALLOC_1 'Aa0001', 18050,'iden','01/01/2016' --- success ful insertion of identification
Aa0001 18050 iden 2016-01-01
exec ALLOC_1 'Aa0001', 18056,'prep','01/01/2016' -- executes success full but data not inserted