Post reply

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

  • 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

  • July 20, 2004 at 3:59 am

    #515190

    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

  • July 20, 2004 at 5:47 am

    #515206

    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

  • July 20, 2004 at 6:06 am

    #515215

    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

  • July 20, 2004 at 6:32 am

    #515228

    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

  • July 20, 2004 at 7:12 am

    #515248

    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

  • July 20, 2004 at 7:45 am

    #515260

    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

  • July 20, 2004 at 7:51 am

    #515263

    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

  • July 20, 2004 at 7:54 am

    #515265

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

    LinkedIn Profile

  • July 20, 2004 at 8:11 am

    #515269

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

  • July 20, 2004 at 8:17 am

    #515271

    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

  • July 20, 2004 at 10:25 pm

    #515401

    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)
    Intro to Tally Tables and Functions

  • July 21, 2004 at 1:26 am

    #515419

    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

  • July 21, 2004 at 2:19 am

    #515434

    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

  • July 21, 2004 at 2:42 am

    #515437

    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

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

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