Exec stored proc from stored proc

[ooOOJaVaOOoo]

I have two stored procs that work together, the first one gathers the IDs from the user input and the other one takes the ids and inserts them into the db

The first SP never seems to call the second SP, how can I make this work?

First SP

ALTER PROCEDURE dbo.spNewTicket
@tTicketID int,
@BusinessUnitName nvarchar(50),
@ServiceName nvarchar(50),
@ResponsibilityOwnerName nvarchar(50),
@ClassificationName nvarchar(50)

AS
DECLARE @BusUnitID Int
DECLARE @ServID Int
DECLARE @RespID Int
DECLARE @ClassID Int

SET @BusUnitID = (select BusinessUnitID from tblBusinessUnit where BusinessUnitName=@BusinessUnitName)
SET @ServID = (select ServiceID from tblService where ServiceName=@ServiceName)
SET @RespID = (select ResponsibilityOwnerID from tblResponsibilityOwner where ResponsibilityOwnerName=@ResponsibilityOwnerName)
SET @ClassID = (select ClassificationID from tblClassification WHERE ClassificationName=@ClassificationName)

EXEC spAddTicketDetail @TicketID=@tTicketID, @BusinessUnitID=@BusUnitID, @ServiceID=@ServID, @ResponsibilityOwnerID=@RespID, @ClassificationID=@ClassID

	RETURN

Second SP

ALTER PROCEDURE [dbo].[spAddTicketDetail]

@TicketID int OutPut,
@BusinessUnitID int,
@ServiceID int,
@ClassificationID int,
@ResponsibilityOwnerID int

AS
BEGIN TRY
IF NOT @TicketID = (SELECT TicketID from [dbo].[tblTicketDetail] WHERE TicketID = @TicketID)

		INSERT INTO [dbo].[tblTicketDetail] VALUES (@TicketID, @BusinessUnitID, @ServiceID, @ResponsibilityOwnerID, @ClassificationID, GetDate())

END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER()
END CATCH
	RETURN

[Tom Sawyer]

I don't think you need to specify what the parameter names are for the second proc. You should just be able to do:

EXEC spAddTicketDetail @tTicketID, @BusUnitID, @ServID, @RespID, @ClassID

Also, you have @TicketID declared as an output parameter, but you're not passing it in as an output parameter, which you need to do. Also, you don't change it in the second sp or use it for anything afterwards, so either remove that flag or change the call to:

EXEC spAddTicketDetail @tTicketID out, @BusUnitID, @ServID, @RespID, @ClassID

[ooOOJaVaOOoo]

So I updated the EXEC line as you have it above. spNewTicket runs, but spAddTicketDetails doesn't seem to fire. Below is the output from running spNewTicket

Running [dbo].[spNewTicket] ( @tTicketID = 123458, @BusinessUnitName = MS&S, @ServiceName = CSTools, @ResponsibilityOwnerName = Infrastructure, @ClassificationName = OSError ).

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[spNewTicket].

[Tom Sawyer]

Does the ticket id 123458 exist in the tblTicketDetail table? It may not be showing anything because it doesn't run that insert statement.

Just before your BEGIN TRY line (not after it) add the line:

raiserror 66666 'This is an error'

Then run the spNewTicket from the Management Studio and see if that error gets thrown. That'll let you know if it's going into your second sp at all.

[alex_read]

To follow up on TOM's suggestions, I'd like to add a point that I normally return a numeric value from stored procedures - a 0 for a success, or a different number -1,-2 etc. for errors encountered. You could also use string values too, but using this, you can bubble the return values both for the calling stored procedure to validate, but also for the calling code to handle. That way you can log the errors, rollback transactions, show messageboxes and handle any errors with more control.

DECLARE @ExecutionStatus int
SET @ExecutionStatus = 0

EXEC @ExecutionStatus = spAddTicketDetail @tTicketID out, @BusUnitID, 
                                                          @ServID, @RespID, @ClassID