Link to home
Create AccountLog in
Avatar of richa1960
richa1960Flag for United States of America

asked on

firing a exec job statement base on select query using a case statement

I'm attempting to create a job will fire an alert job base on the query results from a table column. the
select statement containes a case statement, i'm getting the following error (Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

     SEE CODE BELOW:

select jobename, jobrun, jobrunning =
  case
   when jobrun = 'jobrunning' then 'EXEC SP_start_job @Job_name' = 'Master vela batch job alert I'
   when jobrunning = 'jobalreadydone'then 'EXEC SP_start_job @Job_name' = 'Master vela batch job alert II'
  end
from jobtrack
where jobname = 'master vela batch job'
go
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

try

select jobename, jobrun, jobrunning =
  case
   when jobrun = 'jobrunning' then 'EXEC SP_start_job @Job_name = ''''Master vela batch job alert I'''
   when jobrunning = 'jobalreadydone' then 'EXEC SP_start_job @Job_name = ''''Master vela batch job alert II'''
  end
from jobtrack
where jobname = 'master vela batch job'
I think I added too many quotations

select jobename, jobrun, jobrunning =
  case
   when jobrun = 'jobrunning' then 'EXEC SP_start_job @Job_name = ''Master vela batch job alert I'''
   when jobrunning = 'jobalreadydone'then 'EXEC SP_start_job @Job_name = ''Master vela batch job alert II'''
  end
from jobtrack
where jobname = 'master vela batch job'
Avatar of richa1960

ASKER

great the code parses, but it looks like the job exec is not firing, I test both jobs and they
execute, outside of the sql code.
You do realize that code does not execute anything, right?
Use the Debug option provided in sql server 2008 to track your code weather the SP are firing or not

Try something like this:

select jobename, jobrun, jobrunning =
  case
   when jobrun = 'jobrunning' then
   EXEC SP_start_job @Job_name = 'Master vela batch job alert I'
   when jobrunning = 'jobalreadydone'then
   EXEC SP_start_job @Job_name = 'Master vela batch job alert II'
  end
from jobtrack
where jobname = 'master vela batch job'
go


Exec Commands should not be in quotation
Romasa,

Just curious, but did you test that?
I have Executed an SP not exactly in CASE but in If command and it worked for me. By the way you didnt pin-point your curiosity area.
the code comes back with the following error if set up like below. But if i move the exec statement out on its own it executes just fine.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'EXEC'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'from'.



select jobename, jobrun, jobrunning =
  case
   when jobrun = 'jobrunning' then EXEC SP_start_job @Job_name = 'Master_vela batch job alert I'
   when jobrunning = 'jobalreadydone'then EXEC SP_start_job @Job_name = 'Master_vela batch job alert II'
  end
from jobtrack
where jobname = 'master vela batch job'
go
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>the code comes back with the following error if set up like below. But if i move the exec statement out on its own it executes just fine.<<
Right, you cannot execute a Stored Procedure from a SELECT statement, let alone a CASE.