How can I use a parameter from a stored procedure in another procedure

Question

I am created an online staff rota application. I have a stored procedure for each day of the week with an auto increment primary key for the Rota table. Each day within the selected week will have the same rota ID as a foreign key.

I have no problem with the first selected day but the following days return NULL for the foreign key RotaID as I don't know how to pass the RotaID into the other stored procedures to ensure the RotaID remains the same for the 7 days of the weekly Rota.

CREATE PROCEDURE [usp_RotaDay1]

@Week_Begin datetime, 
@Week_End datetime,

@1ShiftDate datetime,
@1Day nchar (10),

AS
BEGIN
SET NOCOUNT ON;

DECLARE @RotaID int
IF NOT EXISTS 
(
SELECT * FROM Rota 
WHERE Week_Begin = @Week_Begin
AND
Week_End = @Week_End
)
BEGIN

INSERT INTO Rota
 (
 [Week_Begin], 
 [Week_End]
 )

VALUES
 (
 @Week_Begin,
 @Week_End
 )
END

SELECT @RotaID = SCOPE_IDENTITY()
IF NOT EXISTS 
(
SELECT * FROM DayShift
WHERE ShiftDate = @1ShiftDate
)
BEGIN

INSERT INTO DayShift

 (
 [RotaID],
 [ShiftDate],
 [Day]
 )

 VALUES
 (
 @RotaID,
 @1ShiftDate,
 @1Day
 )
END

SET NOCOUNT OFF;
END

As you can see I have the RotaID declared in the Rota table and then passed as the foreign key in the DayShift table.

I would like to know if it is possible to pass this through to my other stored procedures which are similar to this one.

lc. · Accepted Answer · 2013-03-29 14:08:12Z

1

I'm not sure exactly what you are trying to do, but if you want to return the @RotaID parameter value to the calling program for use in a future procedure call, you can specify the parameter as OUTPUT:

CREATE PROCEDURE [usp_RotaDay1]
(
    @Week_Begin datetime, 
    @Week_End datetime,

    @1ShiftDate datetime,
    @1Day nchar (10),
    @RotaID int OUTPUT
)
AS
    ...

answered Mar 29, 2013 at 14:08

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danny Over a year ago

I have tried this although because I declare the RotaID parameter further down in the code I get errors when trying to specify as output. I think I need to reorganise my code in order to set RotaID to output

lc. Over a year ago

@Danny You wouldn't DECLARE it anymore as specifying it as OUTPUT takes the place of a declaration.

Brian · Accepted Answer · 2013-03-29 14:09:24Z

0

In each of your secondary stored procedures, you need to retrieve the primary key id using a select statement. You know weekbegin and weekend for all your secondary procedures right? so you know which row to pull to retrieve the id. Hope I made sense.

answered Mar 29, 2013 at 14:09

Brian

1,3975 gold badges17 silver badges35 bronze badges

1 Comment

Danny Over a year ago

@Brain thanks for this, it has provided me with a quick fix. I just select the RotaID WHERE shiftdate BETWEEN weekbegin and weekend.

Collectives™ on Stack Overflow

How can I use a parameter from a stored procedure in another procedure

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related