SQLTeam.com | Weblogs | Forums

Output is not as expected pls help


#1

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


#2

Could you explain in a few sentences what you want to achieve here? It seems your code is dependent on row counts from the alloc_test table. What should the second execution produce?