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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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