December 28, 2007 at 10:56 am
I am learning how to return a value from a nested SP and so far have the code below:
Will this return the value of the new primary key to the @NoteID variable?
CREATE PROCEDURE usp_Prognote_ProcedureNote
@AppointmentID int
,@PartyID int
,@EmpUpdated varchar(150)
AS
DECLARE @NoteID int
BEGIN
EXEC @NoteID = usp_Prognote_Insert
@SecondaryKeyValue=@AppointmentID
,@EmpName = @EmpUpdated
,@PrimaryKeyValue
END
Thanks,
Sam
December 28, 2007 at 1:45 pm
It depends on how you have implemented the spu_insert.... stored procedure.
Regards,
Felix
December 28, 2007 at 1:47 pm
FelixG (12/28/2007)
It depends on how you have implemented the spu_insert.... stored procedure.Regards,
Felix
Sorry, It depends on how you coded usp_Prognote_Insert ...
December 28, 2007 at 2:03 pm
sorry, should have supplied that to begin with:
CREATE PROCEDURE usp_ProgNote_Insert
@SecondaryKeyValue int
, @EmpName varchar(150)
, @PrimaryKeyValue int OUTPUT AS
IF EXISTS
(SELECT
pn.AppointmentID
FROM ProgNote pn
WHERE pn.AppointmentID = @SecondaryKeyValue
)
RETURN
INSERT ProgNote
(
AppointmentID
, EmpCreated
, EmpUpdated
)
VALUES
(
@SecondaryKeyValue
, @EmpName
, @EmpName
)
SET @PrimaryKeyValue = (SELECT SCOPE_IDENTITY())
GO
December 28, 2007 at 2:34 pm
Since you're actually returning the value of the inserted key in an output var, your stored procedure should look like this:
CREATE PROCEDURE usp_Prognote_ProcedureNote
@AppointmentID int
,@PartyID int
,@EmpUpdated varchar(150)
AS
BEGIN
DECLARE @NoteID int
EXEC usp_Prognote_Insert @AppointmentID, @EmpUpdated, @NoteID OUTPUT
select @NoteID -- this should show you the inserted identity value
END
December 29, 2007 at 8:19 am
Thanks so much. Believe it or not, I spent an hour on Google and never could get a clear example to work from.
December 29, 2007 at 8:41 am
how can I modify usp_Prognote_Insert to return the value of the PK if a record already exists?
This doesn't seem to work:
CREATE PROCEDURE usp_ProgNote_Insert
@SecondaryKeyValue int
, @EmpName varchar(150)
, @PrimaryKeyValue int OUTPUT
AS
DECLARE @NoteID int
IF EXISTS
(SELECT
@NoteID=pn.NoteID
FROM ProgNote pn
WHERE pn.AppointmentID = @SecondaryKeyValue
)
SET @PrimaryKeyValue=@NoteID
December 29, 2007 at 12:44 pm
smknox (12/29/2007)
how can I modify usp_Prognote_Insert to return the value of the PK if a record already exists?This doesn't seem to work:
CREATE PROCEDURE usp_ProgNote_Insert
@SecondaryKeyValue int
, @EmpName varchar(150)
, @PrimaryKeyValue int OUTPUT
AS
DECLARE @NoteID int
IF EXISTS
(SELECT
@NoteID=pn.NoteID
FROM ProgNote pn
WHERE pn.AppointmentID = @SecondaryKeyValue
)
SET @PrimaryKeyValue=@NoteID
What you are doing here is checking for the existence, not setting the variable. So, when you declare the variable @NoteID it will be set to NULL, then you are setting @PrimaryKeyValue = @NoteID if @SecondaryKeyValue equals an AppointmentID.
This can be rewritten a couple of ways:
DECLARE @NoteID int;
SET @NoteID = (SELECT pn.NoteID FROM ProgNote pn WHERE pn.AppointmentID = @SecondaryKeyValue);
IF @NoteID IS NOT NULL
BEGIN
SET @PrimaryKeyValue = @NoteID;
RETURN;
END
Or, you can do something like:
IF EXISTS (SELECT * FROM ProgNote pn WHERE pn.AppointmentID = @SecondaryKeyValue)
BEGIN
SET @PrimaryKeyValue = (SELECT NoteID FROM ProgNote pn WHERE pn.AppointmentID = @SecondaryKeyValue);
END
ELSE
BEGIN
END
Or, you can use a combination of either. It all depends on the requirements for this procedure.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply