Run jobs in SELECT CASE?

Hello!
Is that possible to call a job using SELECT CASE statement? I am trying
to run some jobs based on some cases in my stored procedure. See below
the code:
select case RptName
when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
end
from Table
Trying not to use IF-ELSE statement (which I have tried and it is
working fine).
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***>> Trying not to use IF-ELSE statement
SELECT statement returns a resultset, it is not not meant to execute
procedures. CASE is not supposed to be used as you have suggested. It
returns a scalar value.
Each sp_start_job invocation is a separate statement. Use IF.. ELSE
construct to execute them conditionally.
Anith|||In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
then.. end] is an expression, so you can't execute a procedure from it, but
you can call functions.
However, this would seem to do what you want:
select @.RptName = RptName from Table
if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
Or, looking at your example, it seems that perhaps just this would work:
exec sp_start_job @.job_name = @.RptName
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> Is that possible to call a job using SELECT CASE statement? I am trying
> to run some jobs based on some cases in my stored procedure. See below
> the code:
> select case RptName
> when 'A' then [msdb].[dbo].[sp_start_job] @.job_name = 'A'
> when 'B' then [msdb].[dbo].[sp_start_job] @.job_name = 'B'
> when 'C' then [msdb].[dbo].[sp_start_job] @.job_name = 'C'
> when 'D' then [msdb].[dbo].[sp_start_job] @.job_name = 'D'
> end
> from Table
> Trying not to use IF-ELSE statement (which I have tried and it is
> working fine).
> Thanks for your help!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks, Anith.
*** Sent via Developersdex http://www.examnotes.net ***|||But then so would...
select @.RptName = RptName from Table
exec sp_start_job @.job_name = @.RptName
Just remember to keep the RptName matching the Job name.
Colin Dawson
www.cjdawson.com
"JT" <someone@.microsoft.com> wrote in message
news:OwGor8aBGHA.1864@.TK2MSFTNGP12.phx.gbl...
> In T-SQL, there is no [select case ..] statement. Instead, [case.. when..
> then.. end] is an expression, so you can't execute a procedure from it,
> but you can call functions.
> However, this would seem to do what you want:
> select @.RptName = RptName from Table
> if @.RptName = 'A' exec sp_start_job @.job_name = 'A'
> if @.RptName = 'B' exec sp_start_job @.job_name = 'B'
> Or, looking at your example, it seems that perhaps just this would work:
> exec sp_start_job @.job_name = @.RptName
>
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:eWaWmjaBGHA.1864@.TK2MSFTNGP12.phx.gbl...
>