data entry best practice

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  August 14, 2003 at 4:26 am

  #159840

  Hi,

  I want to perform data entry for an entity that spans multiple tables, eg a ‘store’ entity.

  Below is my take on it.

  Any advice greatly appreciated

  In short, I create a sproc that:

  1) Opens a transaction

  2) INSERTS a store into tblStore

  3) Returns the @@Identity of this newly created store.

  4) Calls the other sprocs, passing the @@Identity to each one.

  5) Closes the transaction

  Below is the ‘meaty’ explanation

  I have several tables:

  tblStore

  storeId PRIMARY KEY INT IDENTITY

  storeName

  etc

  tblStoreProfile

  storeId (REFERENCES tblStore.storeId)

  etc

  tblStoreInventory

  storeId (REFERENCES tblStore.storeId)

  etc

  So, When I want to CREATE a new entity, I create a sproc “spStoreCreate”

  CREATE PROCEDURE spStoreCreate

  @storeName VARCHAR(50)

  etc

  etc

  AS

  BEGIN TRANSACTION

  INSERT INTO

  tblStore ( storeName,

  etc,

  etc )

  VALUES( @storeName

  etc,

  etc )

  DECLARE @storeId INT

  SELECT @storeId = @@Identity

  --call the other sprocs

  EXEC spStoreProfileCreate @storeId

  EXEC spStoreInventoryCreate @storeId

  END TRANSACTION

  GO

  a few words of advice would be very welcome.

  Cheers,

  yogiberr

• Andy Warren

  SSC Guru

  Points: 119747

  More actions

  August 14, 2003 at 4:54 am

  #469899

  Seems ok. Should use scope_identity if you're using SQL2K.

  Andy

  http://qa.sqlservercentral.com/columnists/awarren/

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  August 14, 2003 at 6:50 am

  #469922

  cheers Andy,

  I was previously unaware about 'scope_identity'

  thanks,

  yogi