SQLTeam.com | Weblogs | Forums

Stored procedure with case then statment


#1

create PROC ALLOC
@UID VARCHAR(6),
@EMP_ID INT,
@STAGE_ID varchar(10),
@DATE_OF_ASSIGN DATE
AS
BEGIN

declare
@EMPUID int, @IDEN int,@PREP int,@vali int, @capt1 int,@capt2 int,@comp int, @tqc int ,@sync int

set @EMPUID = (select count() from ALLOCATION where uid = @UID and [EMP ID] = @EMP_ID) -- returns 1 if same UID assigned to same EMP ID
set @IDEN = (select count(
) from ALLOCATION where uid = @UID and [Stage ID] = 'Iden') -- returns 1 if UID assigned to IDEN
set @PREP = (select count() from ALLOCATION where uid = @UID and [Stage ID] = 'PREP') -- returns 1 if UID assigned to PREP
set @VALI = (select count(
) from ALLOCATION where uid = @UID and [STAGE ID] = 'VALI')
set @CAPT1 = (select count() from ALLOCATION where uid = @UID and [STAGE ID] = 'CAPT1')
set @CAPT2 = (select count(
) from ALLOCATION where uid = @UID and [STAGE ID] = 'CAPT2')
set @COMP = (select count() from ALLOCATION where uid = @UID and [STAGE ID] = 'COMP')
set @TQC = (select count(
) from ALLOCATION where uid = @UID and [STAGE ID] = 'TQC')
set @SYNC = (select count(*) from ALLOCATION where uid = @UID and [STAGE ID] = 'SYNC')

case @IDEN = 0
--begin
-- If @EMPUID=0 AND @IDEN = 0 and @PREP=0 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
--begin
then
print 'PLEASE ASSIGN -IDENTIFICATION'
else
print 'hi'
/*
--case
begin
If @EMPUID=0 AND @IDEN = 1 and @PREP=0 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
begin
print 'PLEASE ASSIGN -PREPARATION'
end
case
If @EMPUID=0 AND @IDEN = 1 and @PREP=1 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
begin
print 'PLEASE ASSIGN - VALIDATION'
end
case
when @EMPUID=0 AND @IDEN = 0 and @PREP=0 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
then print 'PLEASE ASSIGN -IDENTIFICATION'

when @EMPUID=0 AND @IDEN = 1 and @PREP=0  and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0  AND @TQC = 0 AND @STAGE_ID = 'SYNC'
then print 'PLEASE ASSIGN -PREPARATION'

when EMPUID=0 AND @IDEN = 1 and @PREP=1  and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0  AND @TQC = 0 AND @STAGE_ID = 'SYNC'
begin 	print 'PLEASE ASSIGN - VALIDATION'

when @EMPUID=0 AND @IDEN =1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 0 AND @CAPT2=0  and @TQC = 0 AND @STAGE_ID = 'SYNC'
begin 	print 'PLEASE ASSIGN - CAPTURING 1'

when  @EMPUID=0 AND @IDEN = 1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 1 AND @CAPT2=0  and @TQC = 0 AND @STAGE_ID = 'SYNC'
begin print 'PLEASE ASSIGN - CAPTURING 2'
 
when  @EMPUID=0 AND @IDEN = 1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 1 AND @CAPT2=1 AND @COMP=1 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
begin print 'PLEASE ASSIGN - TQC'

end

please check the store procedure and correct me if am wrong in syntax or logic.


#2

You can't do this:

case @IDEN = 0
--begin
--	If @EMPUID=0 AND @IDEN = 0 and @PREP=0 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
--begin
then
print 'PLEASE ASSIGN -IDENTIFICATION'
else
print 'hi'

Remember CASE is an expression, not a statement,

You want:

IF @IDEN=0
BEGIN
    print 'PLEASE ASSIGN -IDENTIFICATION'
END
ELSE BEGIN
    print 'hi'
END

#3
...
set @SYNC = (select count(*) from ALLOCATION where uid = @UID and [STAGE ID] = 'SYNC')

declare @print_msg varchar(1000)

set @print_msg = case
when @IDEN = 0 
    then 'PLEASE ASSIGN -IDENTIFICATION'
when @EMPUID=0 AND @IDEN = 1 and @PREP=0 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
    then 'PLEASE ASSIGN -PREPARATION'
when @EMPUID=0 AND @IDEN = 1 and @PREP=1 and @VALI = 0 AND @CAPT1 = 0 AND @CAPT2=0 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
    then 'PLEASE ASSIGN - VALIDATION'
when @EMPUID=0 AND @IDEN =1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 0 AND @CAPT2=0  and @TQC = 0 AND @STAGE_ID = 'SYNC'
    then 'PLEASE ASSIGN - CAPTURING 1'
when @EMPUID=0 AND @IDEN = 1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 1 AND @CAPT2=0  and @TQC = 0 AND @STAGE_ID = 'SYNC'
    then 'PLEASE ASSIGN - CAPTURING 2'
when @EMPUID=0 AND @IDEN = 1 and @PREP=1  and @VALI = 1 AND @CAPT1 = 1 AND @CAPT2=1 AND @COMP=1 AND @TQC = 0 AND @STAGE_ID = 'SYNC'
    then 'PLEASE ASSIGN - TQC'
else '?'
end

print @print_msg