Inserting With Cases

I have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a
case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4'
then 4,
else ..... then 3,
else ....... then 1,
end

that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks
Ludwig

CREATE TABLE #QTEMP(
[JobQueueID][int],
[JobScheduleID][int],
[JobID][int],
[JobName][varchar](50),
[JobDesc][varchar](50),
[JobStepID][int],
[JobStepName][varchar](50),
[JobStepDesc][varchar](50),
[JobStepExecutable][varchar](100),
[JobQueueStatus_ID][int],
[JobQueueStatusDesc][varchar](100),
[NextRunDateTime][datetime],
[LastRunDateTime][datetime],
[ProcessID][int]
)ON[PRIMARY]
Declare @sql nvarchar(4000)
Set @sql='INSERT INTO #QTEMP
SELECT [JobQueueID],
[JobScheduleID],
[JobID],
[JobName],
[JobDesc],
[JobStepID],
[JobStepName],
[JobStepDesc],
[JobStepExecutable],
[JobQueueStatus_ID],
[JobQueueStatusDesc],
[NextRunDateTime],
[LastRunDateTime],
[ProcessID]
FROM [FlexPort].[dbo].[FlexPortJobQueueView]
WHERE [JobID] IS NOT NULL

'
IF ISNull(@JobScheduleID,'')<>''
Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%'''
IF ISNull(@JobID,'')<>''
Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%'''
IF ISNull(@JOBName,'')<>''
Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%'''
IF ISNull(@Status,'')<>''
Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%'''
If IsNull(@LastRunDateTime, '') <>''
Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''


Exec master.dbo.sp_ExecuteSql @sqlI have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a
case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4'
then 4,
else ..... then 3,
else ....... then 1,
end

that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks
Ludwig

CREATE TABLE #QTEMP(
[JobQueueID][int],
[JobScheduleID][int],
[JobID][int],
[JobName][varchar](50),
[JobDesc][varchar](50),
[JobStepID][int],
[JobStepName][varchar](50),
[JobStepDesc][varchar](50),
[JobStepExecutable][varchar](100),
[JobQueueStatus_ID][int],
[JobQueueStatusDesc][varchar](100),
[NextRunDateTime][datetime],
[LastRunDateTime][datetime],
[ProcessID][int]
)ON[PRIMARY]
Declare @sql nvarchar(4000)
Set @sql='INSERT INTO #QTEMP
SELECT [JobQueueID],
[JobScheduleID],
[JobID],
[JobName],
[JobDesc],
[JobStepID],
[JobStepName],
[JobStepDesc],
[JobStepExecutable],
[JobQueueStatus_ID],
[JobQueueStatusDesc],
[NextRunDateTime],
[LastRunDateTime],
[ProcessID]
FROM [FlexPort].[dbo].[FlexPortJobQueueView]
WHERE [JobID] IS NOT NULL

'
IF ISNull(@JobScheduleID,'')<>''
Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%'''
IF ISNull(@JobID,'')<>''
Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%'''
IF ISNull(@JOBName,'')<>''
Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%'''
IF ISNull(@Status,'')<>''
Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%'''
If IsNull(@LastRunDateTime, '') <>''
Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''


Exec master.dbo.sp_ExecuteSql @sql