Thanks in advance for any help on this issue!!
I have tables tracking documents in a library (tblDocuments, tblDocHistory, tblDocLocation). The history and location tables are child tables of the document table (i.e. no entry can exist in them without 1st having a record in the document table.)
Sometimes a document is copied, and I have created a stored proc to duplicate the desired document record, and insert new history and location records for the new document. The stored proc runs fine in query analyzer, but the parts for inserting into the history and location tables produses no insertion. @@error = 0 for both insert attemps. I can only think that the insert into tblDocuments is not completed yet, and so the select statments are returning NULL.
Can anyone tell me if this is the case, and what a solution would be whatever the case?
Create Proc MakeDocRecNextCopy
@ControlNumber char(9)
AS
DECLARE @DocID int
INSERT INTO tblDocuments (
ControlNumber,
CopyNumber,
Title)
SELECT TOP 1
ControlNumber,
CopyNumber + 1,
Title
FROM tblDocuments
WHERE ControlNumber = @ControlNumber
SET @DocID = @@Identity
--This part of the code works fine, the next gives no inserted result
INSERT INTO tblDocHistory (DocID, title, originDate)
SELECT DocID, title, getdate()
FROM tblDocuments
WHERE DocID = @DocID
--location insert is similar
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.