My program runs a number of batch files. I use a parameter table as below to
loop thru what commands should be fired in Exec master..xp_cmdshell @pCommand
;

For ev_id 4,5,6 & 7 I want to add an if statement to only execute one of them.
I can't seem to work out the logic.
With current logic it will execute all 4 as well as the other eight.
I have an if else statement below I currently use but it won't work as the
loop iterates. Can anyone guide me? Please feel free to ask questions in case
i have not explained the issue correctly.

My tables looks as follows:
ev_id ev_name                  yr_id    yr_modulo
----- ------------------------ -------- ---------
1     Weekly Front Sheets1     NULL     NULL
2     Weekly Back Sheets       NULL     NULL
3     Weekly Core Pack         NULL     NULL
4     Weekly Front Sheets2     200601   0.25
5     Weekly Front Sheets3     200602   0.5
6     Weekly Front Sheets4     200603   0.75
7     Weekly Front Sheets5     200604   0.0
8     Weekly Front Sheets6     NULL     NULL
9     Weekly Front Sheets7     NULL     NULL
10    Weekly Front Sheets8     NULL     NULL
11    Weekly Front Sheets9     NULL     NULL
12    Weekly Front Sheets10    NULL     NULL


if @yr_modulo = 0.25
begin
SELECT     @pCommand = @batCommand + @ev_bat_desc
Exec     master..xp_cmdshell @pCommand ;
end
else if @yr_modulo = 0.5
begin
SELECT     @pCommand = @batCommand + @ev_bat_desc
Exec     master..xp_cmdshell @pCommand ;
end
else if @yr_modulo = 0.75
begin
SELECT     @pCommand = @batCommand + @ev_bat_desc
Exec     master..xp_cmdshell @pCommand ;
end
else if @yr_modulo = 0.0
begin
SELECT     @pCommand = @batCommand + @ev_bat_desc
Exec     master..xp_cmdshell @pCommand ;
end
else if @yr_modulo is NULL
begin
SELECT     @pCommand = @batCommand + @ev_bat_desc
Exec     master..xp_cmdshell @pCommand ;
end

Could your show us your whole loop please, I guess the Variable is not
set the right way, so that always the NULL path tis executed. You can
check that with putting some print statements in between the execution
like:_

(...)
else if @yr_modulo is NULL
begin
Print 'Welcome to the NULL path of my procedure' --Or something more
serious :-)
SELECT         @pCommand = @batCommand + @ev_bat_desc
Exec   master..xp_cmdshell @pCommand ;
end
(...)

HTH, jens Suessmeyer.

I have added loop below.
The loop will return 12 items from parameters table.
However I only want it to run 9 items ie all of the NULLS (works fine) and 1
of the 4 ev_id's 4,5,6 or 7


-- Get first .bat file to run.
SELECT     TOP 1 @ev_name = a.ev_name, @ev_bat_desc = ev_bat_desc, @ev_sch_id =
ev_sch_id, @yr_modulo = yr_modulo
FROM     MSTY_lu_events_parameter a, MSTY_lu_events_yearly_parameter b
WHERE    substring(ev_snapshot_id,1,1) in ('W', 'M')    -- if the first letter is
IN W and M then the snapshot id is 'B' [both weekly and monthly].
AND        a.ev_id = b.ev_id
AND        ev_Set = 'N'
AND        ev_Switch = 'Y'
ORDER BY ev_seq
IF (@@ROWCOUNT < 1)
    begin
        select @text = 'Error -100: Could not retrieve MSTY Events parameter file
for status NOT SET.'
        select @result = -100
        GOTO ERROR_POINT               
    end

WHILE @chJob_Complete = 'N'
    BEGIN

    UPDATE     MSTY_lu_events_parameter
    SET        ev_Set = 'Y'
    WHERE    ev_name = @ev_name
    AND        ev_Set = 'N'
    AND        ev_Switch = 'Y'
    if @@ERROR <> 0 or @@ROWCOUNT = 0
        begin
            select @text = 'Error -110: Could not update MSTY Events parameter file
for status SET.'
            select @result = -120
            GOTO ERROR_POINT
        end

if @yr_modulo = 0.25
begin
    print '0.25   ' + @ev_name
    INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ', 'STATUS',
'Firing xp_cmdshell [psExec - ' + @ev_name + '] Trigger to SRV-CQAREP04...')       
    SELECT     @pCommand = @batCommand + @ev_bat_desc
    Exec     master..xp_cmdshell @pCommand ;
end
    else if @yr_modulo = 0.5
        begin
            print '0.5    ' + @ev_name
            INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', 'Firing xp_cmdshell [psExec - ' + @ev_name + '] Trigger to
SRV-CQAREP04...')       
            SELECT     @pCommand = @batCommand + @ev_bat_desc
            Exec     master..xp_cmdshell @pCommand ;
        end
        else if @yr_modulo = 0.75
            begin
                print '0.75   ' + @ev_name
                INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', 'Firing xp_cmdshell [psExec - ' + @ev_name + '] Trigger to
SRV-CQAREP04...')       
                SELECT     @pCommand = @batCommand + @ev_bat_desc
                Exec     master..xp_cmdshell @pCommand ;
            end
            else if @yr_modulo = 0.0
                begin
                    print '0.0    ' + @ev_name   
                    INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', 'Firing xp_cmdshell [psExec - ' + @ev_name + '] Trigger to
SRV-CQAREP04...')       
                    SELECT     @pCommand = @batCommand + @ev_bat_desc
                    Exec     master..xp_cmdshell @pCommand ;
                end
                else if @yr_modulo is NULL
                    begin
                        print 'NULL   ' + @ev_name
                        INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', 'Firing xp_cmdshell [psExec - ' + @ev_name + '] Trigger to
SRV-CQAREP04...')       
                        SELECT     @pCommand = @batCommand + @ev_bat_desc
                        Exec     master..xp_cmdshell @pCommand ;
                    end

    -- Inner Loop to Poll above Job
    WHILE @intAttempts_To_Fetch <= @trigger_attempts And @chPoll_Complete = 'N'
    BEGIN       

        SELECT     @job_status_id = a.EM_JOB_STATUS_ID, @job_name = d.IS_SCHED_NAME
        FROM     IS_REP_FACT a(nolock), IS_SESSION b(nolock), EM_JOB_STATUS
c(nolock), IS_SCHED d(nolock)
        WHERE    a.IS_REP_JOB_SES_ID = b.IS_SESSION_ID
        AND        a.EM_JOB_STATUS_ID = c.EM_JOB_STATUS_ID
        AND        a.IS_SCHED_ID = d.IS_SCHED_ID
        AND        b.EM_CONNECT_SOURCE = @connect_source                                                    -- connection
source:     check this before move to Prod
        AND     a.IS_SCHED_ID = @ev_sch_id                                                                -- schedule id:         check
this before move to Prod
        AND     convert(smalldatetime,convert(char(12),a.IS_REP_EXEC_FN_TS)) =
convert(smalldatetime,convert(char(12),getdate()))
        GROUP BY a.EM_JOB_STATUS_ID, d.IS_SCHED_NAME
        IF (@@ERROR <> 0)
            begin
                select @text = 'Error -130: Could not retrieve Microstrategy Enterprise
Manager Data.'
                select @result = -130
                GOTO ERROR_POINT
            end

        if @job_status_id = @ev_job_comp_status_id
        Begin   
            INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', '[' + @ev_name + '] Complete, retrieving next file to process...')       
            SET @chPoll_Complete = 'Y'
        End
        Else Begin
            INSERT INTO dss_log VALUES (getdate(), 0, 0, 'POLL_MSTY_EVENTS ',
'STATUS', 'Polling MSTY Events: [' + @ev_name + '] Poll Attempt: ' +
CONVERT(VARCHAR, @intAttempts_To_Fetch))   
        End

        -- If it gets here then this means Poll not ready
        SELECT @wait_string = '00:' + RIGHT('00' + CONVERT(VARCHAR,
@trigger_delay), 2)+':00'
        WAITFOR DELAY @wait_string -- '00:00:05' -- @trigger_delay  -- (wait time
defined as a parameter)
        SET @intAttempts_To_Fetch = @intAttempts_To_Fetch + 1

        CONTINUE;

    END

    SELECT     TOP 1 @ev_name = a.ev_name, @ev_bat_desc = ev_bat_desc, @ev_sch_id
= ev_sch_id, @yr_modulo = yr_modulo -- outside
    FROM     MSTY_lu_events_parameter a, MSTY_lu_events_yearly_parameter b
    WHERE    substring(ev_snapshot_id,2,1) = 'B'
    AND        a.ev_id = b.ev_id
    AND        ev_Set = 'N'
    AND        ev_Switch = 'Y'
    ORDER BY ev_seq
    IF (@@ROWCOUNT < 1)
        Begin
            SET @chJob_Complete = 'Y'
        End
        Else
        Begin
            CONTINUE;
        End
    END

I just had a thought.
If I do the following outside the loop:

select * from get_lu_snapshot
week_id     month_id    date_id                                             

----------- ----------- -------------------
200601      200601      2006-01-07 00:00:00

declare @snap_modulo float
select @snap_modulo = (week_id % 4) / convert(float, 4) from get_lu_snapshot
print @snap_modulo

and then use

if @yr_modulo = @snap_modulo
begin
....
end
else if @yr_modulo = NULL
begin
....
end

that should work I think.
Let me know what you think.

Sorry that I dont´ have the patience reading the whole script, but if
you loop though the script you have to save the status in some way,
that you went in the NOT NULL branch. Otherwise for each loop a
different brnach of the NOT NULLs will be executed. For debugging
purposes put some PRINT statements in between the command, to see how
the values are evaluated.

HTH, Jens Suessmeyer.

inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)
can't insert into ...because of nulls problme!
Return random number from trigger