SQLTeam.com | Weblogs | Forums

Calling Stored Procedure from another Stored Procedure


#1

I have one Stored Procedure (STOREDP1) that is executed by a scheduled Job and that sends a couple of emails. Once this is done, I want a second Stored Procedure (STOREDP2) to be launched.
I read quite a few articles on the Internet but can't see how to make it work properly.

I did a third Stored Procedure and called both Stored Procedures from there. Although this works it is not a solution for me, as I don't have the rights/privileges to create a new Job. The scheduled Job that was set up for me is calling STOREDP1 so that Stored Procedure has to be the starting point.

These are the last lines of StoredP1:

    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = '....',
    @recipients = ...,
    @body = @Content,
    @subject = 'Quotation Follow-up';

  FETCH NEXT FROM @MyCursor 
  INTO @MyField 
END; 
CLOSE @MyCursor;
CLOSE @CursorQuo;	
DEALLOCATE @MyCursor;
DEALLOCATE @CursorQuo;
EXEC dbo.STOREDP2
END;
END

The Message I receive says:

22 row affected
Mail (Id: 1234) in queue
...
Msg 207, Level 16, State 1, Procedure STOREDP2, Line 15
The name of the column 'Contact' is not valid

That error for the column that is not valid is thrown for all columns of STOREDP2.

Any suggestion?
Martin


#2

If you hope to get a useful answer please post the script for each stored procedure, DDL wfor tables they query and simple data in the form of inserts.


#3

Thank you! You gave me the right hint: I was convinced that this was a general issue on T-SQL not being asynchronous. You made me look at the code again and I my first guess was the right one: I was using the same temp table name in both procedures and it didn't like that!