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
Who is Participating?
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.
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!
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?
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.