CODEWITHSUNDEEP
sqlsql-servert-sql
Darren
Darren

Reputation: 537

Selecting from stored procedure output

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.

Upvotes: 3

Views: 1312

Answers (2)

David Tansey
David Tansey

Reputation: 6013

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269447

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.

Upvotes: 1

Related Questions