Execute a stored procedure and save the result in a table

Posted on 2011-10-23
Last Modified: 2012-05-12
I have few stored procedures.I also have a Result table with stored proc name,result and execution_time as columns. I have to execute the stored procs and save the result in results table with the execution time
Question by:himabindu_nvn
    LVL 59

    Expert Comment

    by:Kevin Cross
    So the results come back in a single column result set? If so, then you can set the execution_time field to default of GETDATE() and then do something like:

    INSERT INTO ResultTable(TheResult)
    EXEC your_stored_proc_name

    Open in new window

    If there is an OUTPUT parameter, then you can get that back first then do the INSERT, but based on the question it sounded like you do not already have a mechanism to see the result...

    Hope that helps!

    Author Comment

    I do not know the result until I execute it. So that means I have to execute each procedure and note down its time and pass the result to the result field?
    LVL 59

    Expert Comment

    by:Kevin Cross
    Not following you. How is the procedure returning results? Is it to a variable or is it a result set with one column  (maybe one column in one row)? You can use either the method I showed above or you can grab the OUTPUT value and then do an INSERT. Your last comment sounds like you are manually writing down a time.

    Author Comment

    I think I was not clear . I am supposed to write test cases to check if the stored procs are working fine. And for some reason I have to create a result table and execute the stored procs and insert the result if it is a success or a failure with the timestamp.
    LVL 59

    Accepted Solution

    What is the code of the procedures and/or the result? i.e., how is success or failure determined? If it is something that can be programmed, you can for example catch the return value of the stored procedure and analyze for success or failure.

    Here is an example:

    If the analysis is more qualitative, then not sure what can be done beyond doing a normal insert.

    INSERT INTO ResultTable(ResultColumn, ExecutionTime)
    VALUES('your enter this value manually', GETDATE());

    You could enter the date/time manually also. Plus if result is a BIT for true or false, then that would be 1 or 0.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now