Stored Procedure Best Practices 1

[HoustonGuy]

I've got about five scripts that perform different tasks that finally generate a flat file in the end.
I need to move these to Stored Procedures so they can be used by others. What's the best practice for this?

Do I trigger all of the SPs from one main SP?

From one main SP
EXEC sp_1
EXEC sp_2
EXEC sp_3
EXEC sp_4
EXEC sp_5

Also, what is the best method for relaying success/failure for each SP back to me? (Something that tells me SP1 was successful or the error, SP2 was successful or the error, and so on.)

Rudimentary question, but just wanted to make sure I was using the best practice for this task.
Thanks!

 

[gmmastros]

Are any of the 5 steps reusable by other parts of your code? If not, why not put all the code in to a single SP?

I have a couple places in code where I do something similar (multiple sp's called from 1 SP). In those instances, I use the return value from the stored procedure. Return values are limited to Integers, and are usually reserved for status (which is what you want it for anyway).

So....

Declare @Ret1 Int,
        @Ret3 Int,
        @Ret4 Int,
        @Ret5 Int,
        @Ret6 Int

EXEC @Ret1 = sp_1
-- Check return value here

EXEC @Ret2 = sp_2
-- Check return value here

EXEC @Ret3 = sp_3
-- Check return value here

EXEC @Ret4 = sp_4
-- Check return value here

EXEC @Ret5 = sp_5
-- Check return value here

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Thanks George!