getting individual values from stored procedure results

[DarwinIT]

For example: say I run msdb..sp_help_job
Can I write the contents of the resultset to a temp table so I can pull out the individual values within the sproc so I can do something based on the values?

 

[gmmastros]

Yes.

You can use insert/exec to accomplish this.

Step 1, create a temp table with the same structure as the stored procedure output.

Step 2, use an Insert/Exec

Step 3, use the temp table

Ex:

[tt]
Create table #Blah(Job_ID Int, Originating_Server VarChar(100), etc.....)

Insert Into #Blah Exec msdb..sp_help_job

Select job_id From #Blah
[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DarwinIT]

Great! Thanks, George!

I did run into a couple of issues implementing this with sp_help_job but came up with what I needed (knowing if a job was currently executing) through this shortcut.

DECLARE @xp_results TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL, current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)


declare @can_see_all_running_jobs int
declare @job_owner sysname
set @can_see_all_running_jobs = 1
insert into @xp_results
exec master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs,@job_owner
select * from @xp_results