How to work with result set from exec in a stored procedure?

Question

Post reply

How to work with result set from exec in a stored procedure?

schwarz

Old Hand

Points: 368
More actions
July 20, 2004 at 2:10 am

#108507

Hi everybody,
I've got the following problem: In a stored procedure, I have to execute another stored procedure, which returns a result set.
How can I work with this result set in my main stored procedure?
E.g. I need to check, if a job is running.
with
exec sp_help_job @job_name='job', @job_aspect='JOB'
I'll get a result. The next step depends on the status value in the result set. How can I get this row?
Hope someone can help,
Thilo

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I think that you can do the following:

SELECT (EXEC sp_help_job @job_name='job', @job_aspect='JOB') INTO #tempTable

Or some variant thereof. Should be able to find more info in BOL OR...

You could query sysjobhistory and sysjobsteps both in master (I think) and get the information by simple SELECT statements...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

schwarz Old Hand Points: 368 More actions · Answer 2

Thank you AJ, but sadly I get a syntax error.

Isn't there any possibilty to work with result sets returning from stored procedures inside another stored procedure? In BOL I haven't found anything about that.

Thanks anyway,

Thilo

David.Poole SSC Guru Points: 76000 More actions · Answer 3

If you know the structure of the recordset returned by a stored procedure then you can build a temporary table of the same structure.

For example, let us suppose that my stored procedure returns an Integer Id and VARCHAR(50) description.

CREATE TABLE #Tmp(Id Int , Description VARCHAR(50))

INSERT #Tmp(Id , Description)

exec usp_MyProc @Arg1, @Arg2 ......@Arg99.

Note that you cannot have the same construct in the usp_MyProc. SQL will warn you if you try and nest these things together.

LinkedIn Profile

schwarz Old Hand Points: 368 More actions · Answer 4

Hi David,

think, your way is the right on. But how to solve the nested error?

Instead of creating a temporary table explicitly, could I use INSERT INTO?

Thanks a lot,

Thilo

David.Poole SSC Guru Points: 76000 More actions · Answer 5

I think you mean SELECT INTO.

I don't know because SELECT INTO is on my list of "avoid like plague" constructs.

It has huge performance overheads which is why I never use it.

The only way around the nesting problem is to design your queries to work in a flat hierarchy. As you can have one stored procedure running multiple SQL statements there is usually a way around this.

LinkedIn Profile

schwarz Old Hand Points: 368 More actions · Answer 6

Yes your are write, SELECT INTO isn't a good way!

I tried it with creating a temp table. No I've the problem, that sp_help_job needs some parameters to reduce the result set to a flat one, eg:

insert #tblJob (job_id, current_execution_status)

exec sp_help_job @job_name='jobname', @job_aspect='JOB'

And here is the problem. I don't know how to set the parameter for sp_help_job without getting an syntax error.

Hope, I don't annoying you and you have got another hint for me.

Thanks a lot,

Thilo

David.Poole SSC Guru Points: 76000 More actions · Answer 7

sp_help_job is in the msdb database therefore change you statement to

insert #tblJob (job_id, current_execution_status)

exec msdb.dbo.sp_help_job @job_name='jobname', @job_aspect='JOB'

LinkedIn Profile

David.Poole SSC Guru Points: 76000 More actions · Answer 8

I forgot to add, your temporary table structure has to match the sp_help_job returned recordset structure.

LinkedIn Profile

schwarz Old Hand Points: 368 More actions · Answer 9

Do I need the whole cols returning by sp_help_job in the table structure, or only these I need?

David.Poole SSC Guru Points: 76000 More actions · Answer 10

You need all of them.

If you don't know what the structure is i.e. is notify_level_eventlog an int, smallint, byte it doesn't matter because SQL is intelligent enough to do an implicit cast provided that the data will fit in the datatype chosen for your specific table.

i.e. an int holding 32767 will fit into a smallint with no complaints, but 32768 won't.

LinkedIn Profile

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

You may have better luck with OpenRowset. Here's a copy of the example in "Books On Line"...

A. Use OPENROWSET with a SELECT and the Microsoft OLE DB Provider for SQL Server

This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs

GO

SELECT a.*

FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David.Poole SSC Guru Points: 76000 More actions · Answer 12

Personally I am not ecstatic about anything that involves coding logins and passwords into stored procedures.

If you use sp_helptext on a stored procedure you get all the code for the stored procedure returned.

Although you could encrpyt the stored procedure this makes maintenance a bit of a nightmare.

LinkedIn Profile

schwarz Old Hand Points: 368 More actions · Answer 13

Thanks a lot to all for helping me!

The easiest way I tried is to call a stored procedure from my client programm, which is calling sp_help_job with the fix parameter I need. So I can work with a recordset to get some status info. Its not very nice, I know, but easy to implement.

But know I have some security stuff, I think. For sa user it works as expected. But for my database users with no sa permissions, it failed logicly.

Is there any possibility to change the security context within an stored procedure to do some stuff, only can do by eg. sysadmin? (similar to use su in linux, or runas in windows)

Thx to all,

Thilo

David.Poole SSC Guru Points: 76000 More actions · Answer 14

Books on-line says that any user who is in the public group within MSDB can list the jobs that they own.

If you want to list jobs you don't own then you have to be in the SYSADMIN role.

Do not use the SA login if you can possibly avoid it.

LinkedIn Profile