• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Execute a stored procedure and save the result in a table

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
0
himabindu_nvn
Asked:
himabindu_nvn
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
himabindu_nvnAuthor Commented:
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?
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
himabindu_nvnAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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: http://www.4guysfromrolla.com/webtech/tips/t100901-1.shtml

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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now