exiting a stored procedure

Question

exiting a stored procedure

marcjason

Mr or Mrs. 500

Points: 501
More actions
January 15, 2014 at 10:23 am

#303239

I have a stored procedure which executes multiple stored procedures.
I want it to run each stored procedure and if the result for any of the stored procedures is not null I want it to stop.
What is the best method for doing this?

Viewing 6 posts - 1 through 6 (of 6 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could use @@ROWCOUNT to validate resultset and GOTO or RAISEERROR to stop the procedure.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

HanShi SSC-Dedicated Points: 33519 More actions · Answer 2

Luis Cazares (1/15/2014)
You could use @@ROWCOUNT to validate resultset and GOTO or RAISEERROR to stop the procedure.

Keep in mind you need to read the @@Rowcount directly after the command you want the result from. When you execute the next command the @@Rowcount will be set to the value of this next command. It is advisible to store the @@Rowcount value into a variable you defined and use this variable in the remaining part of the code.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

marcjason (1/15/2014)
I have a stored procedure which executes multiple stored procedures.
I want it to run each stored procedure and if the result for any of the stored procedures is not null I want it to stop.
What is the best method for doing this?

What do you mean by result? Does each stored proc return data via a select statement? If so, do ALL of them have the exact same structure? I think you probably should post a few more details so we can help you figure out a solution that matches your situation.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

marcjason Mr or Mrs. 500 Points: 501 More actions · Answer 4

certainly. I want to start simple so I'll start with multiple stored procs each one with a select query in. In reality I'll probably make it more complicated as I learn more but as a starting point let's stick with just select queries.

Each stored proc should return nothing. Each one is identifying problems with a database. If there is no problem then I want to move on to the next stored proc. If you require any more info I'm happy to give it. just let me know what youd like.

Thanks

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

marcjason (1/15/2014)
certainly. I want to start simple so I'll start with multiple stored procs each one with a select query in. In reality I'll probably make it more complicated as I learn more but as a starting point let's stick with just select queries.
Each stored proc should return nothing. Each one is identifying problems with a database. If there is no problem then I want to move on to the next stored proc. If you require any more info I'm happy to give it. just let me know what youd like.
Thanks

If you're using a stored procedure to find problems with a current database, why make them into multiple stored procedures? You could go with a single procedure and have multiple checks in it, using RAISERROR when a problem is found. Just because you raise an error doesn't mean you can't continue on. For example:

--the main_table object must exist

if (select object_id('main_table', 'u') is null

raiserror('Table main_table does not exist', 16, 1);

--the child_table object must exist

if (select object_id('child_table', 'u')) is null

raiserror('Table child_table does not exist', 16, 1);

--continue checking for the number of rows in a table, the presence of a view or a specific user's permission

You can use this approach to report all the errors at once. If you have any informational messages that don't raise to the level of being an error, you can use the PRINT statement to print informational messages.

If, on the other hand, you're looking to have a bunch of stored procedures so one check each and have a master procedure call them all in succession, you could write each procedure to return a code of 0 for no problem or another value to indicate a problem was found. Personally, I've found the first approach to work well for me.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions