data entry best practice
-
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
-
Seems ok. Should use scope_identity if you're using SQL2K.
Andy
-
cheers Andy,
I was previously unaware about 'scope_identity'
thanks,
yogi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply