Selecting from stored procedure output

Question

I am trying to explore the possibility of selecting from a stored procedure.

Something like this

SELECT name
FROM exec msdb..sp_help_job
WHERE name = 'SampleJob'

I understand SQL Server - SELECT FROM stored procedure that a user-defined function or view can be used, but these are not options for me.

Reason being I am not able to run the following SQL statement due to permission limitations on AWS-RDS.

SELECT name as Jobs
FROM msdb..sysjobs

This leaves me with no choice but to use msdb..sp_help_job.

What I am ultimately trying to achieve is this "If job is not created, then run create job script". The reason I need to select from the stored procedure is to see if the job exists.

Appreciate any advice / directions.

Pass the parameter value ('SampleJob') to your SP and return the result. — Thit Lwin Oo, Jan 28 '16 at 00:39
Use a temporary table. See http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — Duncan Howe, Jan 28 '16 at 00:43
Is there anyway to do this without having to create any temp tables? — Darren, Jan 28 '16 at 01:05

score 1 · Accepted Answer · answered Jan 28 '16 at 02:24

If you want to create something, but are concerned that it might already exist, then use try/catch blocks.

begin try
    exec dbo.sp_add_job . . .  
end try
begin catch
    print 'Error encountered . . . job probably already exists'
end catch;

To be honest, I haven't done this with jobs/job steps. However, this is one way of re-creating tables, views, and so on.

score 1 · Answer 2 · answered Jan 28 '16 at 02:46

According to the documentation for sp_help_job on MSDN this stored procedure has a @job_name parameter and a simple return code (0 = success or 1 = failure).

If you set the @job_name parameter on your call to sp_help_job and get the return code you should be able to test the value of the return code to accomplish what you want.

Something like this should work:

DECLARE @return_value int

EXEC @return_value = msdb..sp_help_job @job_name = 'MyJobName'

-- @return_value = 1 means the specified @job_name does not exist
IF @return_value = 1 
BEGIN
    --  run create job script
END

Selecting from stored procedure output

2 Answers2