Call Multiple stopred procs from a stored proc

johnnyg123
johnnyg123 used Ask the Experts™
on
I know it is possible to called stored procs from a stored proc by using the exec statement

I have a stored proc that has the following statements


EXEC spDeptAuditReportEmails_RetrieveAllData


EXEC spDeptAuditReportEmails_FilterDataByDepartment


How can I check to make sure the return code from spDeptAuditReportEmails_RetrieveAllData is successful before calling

EXEC spDeptAuditReportEmails_FilterDataByDepartment
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Stored procedures in SQL Server return a default value of zero on success: -99 through -1 are reserverd for system-generated error values. You can return your own error indications by using the RETURN statement with a value. For more info on returning values from a stored procedure, see this article.

Addressing your question specifically, use this:

declare @intRetVal  int
EXEC @intRetVal = spDeptAuditReportEmails_RetrieveAllData
if @intRetVal = 0
begin
   EXEC spDeptAuditReportEmails_FilterDataByDepartment
end

You don't actually need the begin and end around the call to the second sproc,, but it makes it clearer what you're doing (IMHO!).

hth

Mike

Author

Commented:
Perfect ....Thanks Mike!

Commented:
My pleasure!

Have a great weekend

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial