Still need SP help

Question

Still need SP help

smknox

Hall of Fame

Points: 3275
More actions
January 28, 2005 at 4:37 pm

#165879

I'm a novice at writing SPs. This is for adding new records to all three tables. Here is what I think you need of DDL:
Facility table
PK is FacilityID int
Address table
AddressID int
FacilityAddress table
AddressID int
FacilityID int
I want to return a value from one SP to another.
In the 2nd SP below, 0 is passed in as @secondaryKeyValue initially.
I then call another SP that inserts a new record into the Facility table and want the PK back from that SP to put in the FacilityAddress table.
CREATE PROCEDURE usp_Facility_Insert
(
    @SecondaryKeyValue int
, @EmpName varchar(150)
, @PrimaryKeyValue int OUTPUT -- the new generated value will be returned as an output parameter
&nbsp
AS
DECLARE @locFacilityID varchar (20) --this data type works with the ADO class code
SELECT @locFacilityID=f.FacilityID
FROM Facility f
WHERE f.FacilityID = @SecondaryKeyValue

IF @@ROWCOUNT <> 0
BEGIN
SET @PrimaryKeyValue = @locFacilityID
RETURN
END
INSERT INTO [Facility]
(
   [FacilityName]
&nbsp
VALUES
(
   somethingHere
&nbsp
SET @PrimaryKeyValue = (SELECT SCOPE_IDENTITY())

CREATE PROCEDURE usp_FacilityAddress_Insert
   @SecondaryKeyValue int
, @EmpName varchar(150)
, @PrimaryKeyValue int OUTPUT AS
DECLARE @locAddressID int
EXEC usp_Facility_Insert @SecondaryKeyValue, @EmpName, @SecondaryKeyValue
...
--How do I get the value of @PrimaryKeyValue from the Facility table returned?
--
sam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

In your code above the @PrimaryKeyValue variable will only accept the value from the SP call if you explicitly tell it that the parameter is an output parameter.

CREATE PROCEDURE usp_FacilityAddress_Insert

@SecondaryKeyValue int

, @EmpName varchar(150)

, @PrimaryKeyValue int OUTPUT AS

DECLARE @locAddressID int

EXEC usp_Facility_Insert @SecondaryKeyValue, @EmpName, @SecondaryKeyValue OUTPUT -- explicitly lable the variable as accepting output data.

SELECT @PrimaryKeyValue -- This should now be populated

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 2

smknox,

When you call a stored procedure with an output variable you need to assign the ouptput variable of the called stored procedure to something.

I'm a little confused in your example as to what variable you want to assign the value of the output variable from the called stored procedure to. For the purpose of my example, I will assume it is @PrimaryKeyValue. In the example the first value of the "=" pair is the name of the called procedure's parameter, and the second value of the pair is the local variable.

EXEC usp_Facility_Insert @SecondaryKeyValue = @SecondaryKeyValue, @EmpName = @EmpName, @PrimaryKeyValue = @PrimaryKeyValue Output

This call will return a value to the calling stored procedure's local variable @PrimaryKeyValue.

By the way, unless you are outputting the value of @PrimaryKeyValue from the calling stored procedure, you don't need to declare it as OUTPUT in the calling stored procedure.

smknox Hall of Fame Points: 3275 More actions · Answer 3

Thanks, Edwin.

I didn't post the rest of the calling SP, but I am outputting the Address table PK as PrimaryKeyValue there. So I want the incoming PK from the Facility table to be called SecondaryKeyValue (maybe I should just label it FacilityPK). So from what I see above, I could write:

Exec usp_Facility_Insert @SecondaryKeyValue, @EmpName, @PrimaryKeyValue=@SecondaryKeyValue Output

Correct?

Sam

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 4

That should work. Just remember that it will change the value of @SecondaryKeyValue in the calling procedure.