CODEWITHSUNDEEP
sqlsql-serverstored-procedures
ShaunK
ShaunK

Reputation: 1221

How to call a FK value that is a PK value in another table for SQL server procedures

I have 2 procedures: usp_inserterpartner and usp_insertlocation.

usp_insertpartner inserts a row called TradingPartnerPK every time when its called.

usp_insertlocation inserts a row with LocationPK as the PK and TradingPartnerFK as the FK.

How do I exec usp_insertlocation @TradingPartnerFK = the TradingPartnerPK in this case??

My current code is the following:

    CREATE PROCEDURE usp_insertLocation

    @LocationPK int,
    @TradingPartnerFK int,
    @Account varchar (255),
    @Region varchar (255)

    AS
    BEGIN
    SELECT @TradingPartnerFK = Trading_Partner.
    INSERT INTO Location (LocationPK,TradingPartnerFK,Account,Region)
    VALUES (@LocationPK,@TradingPartnerFK,@Account,@Region)

    END

exec usp_insertLocation 1, @TradingPartnerFK = ??????????, 'Nike', 'Americas'

Thanks!!

Upvotes: 1

Views: 84

Answers (3)

Yan Brunet
Yan Brunet

Reputation: 4887

SCOPE_IDENTITY() will return the latest identity created for the last statement in the current scope.

More information here : http://msdn.microsoft.com/fr-fr/library/ms190315(v=sql.100).aspx

The way you will be able to use it actually depends on how you are doing the insert.

If you are within the first StoredProcedure and use SCOPE_IDENTITY() as I said before :

Since you can't use a function in your exec command you will have to do something like this :

declare @fkid int 
set @fkid = SCOPE_IDENTITY()
exec usp_insertLocation 1, @TradingPartnerFK = @fkid, 'Nike', 'Americas'

If the second precedure is called from an outside process such as :

exec usp_insertpartner [...]
exec usp_insertlocation [...]

You will have to use @@identity since you are outside of the initial scope.

So your call would look like this : 

exec usp_insertLocation 1, @TradingPartnerFK = @@identity, 'Nike', 'Americas'

Upvotes: 2

JeffO
JeffO

Reputation: 8043

If you're doing this in separate SP's, your usp_inserterpartner should return the Scope_Identity() value (PK of partner) after it executes an insert statement or use an output parameter to hold this value.

Then when you execute the location sp, the PK value is available either as the return value of usp_inserterpartner or one of it's output parameters.

Upvotes: 0

shibormot
shibormot

Reputation: 1638

using of scope_identity() with identity columns as primary keys example:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table TradingPartner (
  PartnerPK int not null identity(1,1) primary key,
  SomePartnerData varchar(100)

  )

create table Location(
  LocationPK int not null identity(1,1) primary key,
  TradingPartnerFK int not null,
  SomeLocationData varchar(100),
  foreign key (TradingPartnerFK) references TradingPartner (PartnerPK)
  )

go

create procedure usp_insert_partner
  @SomePartnerData varchar(100)
as
begin
  declare @PartnerPK int
  insert TradingPartner (SomePartnerData) values (@SomePartnerData)
  set @PartnerPK = scope_identity()
  return @PartnerPK
end

go

create procedure usp_insert_location
  @TradingPartnerPK int,
  @SomeLocationData varchar(100)
as
begin
  declare @LocationPK int
  insert Location (TradingPartnerFK, SomeLocationData) 
    values (@TradingPartnerPK, @SomeLocationData)
  set @LocationPK = scope_identity()
  return @LocationPK
end

Query 1:

declare @PartnerPK int

exec @PartnerPK = usp_insert_partner 'Some Partner 1'

exec usp_insert_location @PartnerPK, 'Some Location 1'

exec @PartnerPK = usp_insert_partner 'Some Partner 2'

exec usp_insert_location @PartnerPK, 'Some Location 2'

select *
from Location l
  join TradingPartner p on l.TradingPartnerFK = p.PartnerPK

Results:

| LOCATIONPK | TRADINGPARTNERFK | SOMELOCATIONDATA | PARTNERPK | SOMEPARTNERDATA |
----------------------------------------------------------------------------------
|          5 |                4 |  Some Location 1 |         4 |  Some Partner 1 |
|          6 |                5 |  Some Location 2 |         5 |  Some Partner 2 |

Upvotes: 2

Related Questions