November 25, 2003 at 9:12 am
I will appreciate any help on the following:
I have to insert new records in three different tables via store proc. All three have an identity field which is not knowing at the time the process starts.
The first table (case) insert the record and creates a caseid, that caseid has to be carry out to the third table (no problem there) Before this happens, the second table (name) needs two name records and also has an identiyt field, which is carry out to the third table also. The last step is to insert two record to the third table (casename). I wrote a query that does that without any problem.
What I need help is with the store proc. I need to declare a variable @casenumber, which is the only information the end user have at the time he/she initiates the new case process. For the casenumber, no problem, but for the second and third tables, the variable is not known and needs to be declared. How can I declare that variable in the next step of my insert?????
POsting the query script in case anyone has a suggestion. Thank you in advance.
DECLARE @Casenumber as char(20)
SET @CASENUMBER = '9731285'
INSERT INTO tblCaseData
(CaseNumber, DisplayCaseNumber)
VALUES (@CASENUMBER, (left(@casenumber, 2))+'-'+rtrim(Right(@casenumber,18)))
GO
declare @casenumber char(20)
select @casenumber = '9731285'
INSERT INTO tblname (longname)
values (@casenumber+' '+ 'Debtor1')
go
declare @casenumber char (20)
select @casenumber = '9731285'
INSERT INTO tblCasename (caseid, NameID, NameTypeID)
(select caseid, (Select NameID
from tblname
where longname =(@casenumber+' '+ 'Debtor1')), '5'
from tblcasedata
where casenumber = @casenumber)
GO
declare @casenumber char(20)
select @casenumber = '9731285'
INSERT INTO tblname (longname)
values (@casenumber+' '+ 'Debtor2')
go
declare @casenumber char (20)
select @casenumber = '9731285'
INSERT INTO tblCasename (caseid, NameID, NameTypeID)
(select caseid, (Select NameID
from tblname
where longname =(@casenumber+' '+ 'Debtor2')), '6'
from tblcasedata
where casenumber = @casenumber)
GO
November 26, 2003 at 8:54 am
Thank you all. I did figured it out!
November 28, 2003 at 5:51 am
Sorry, you figured it out before I even read it!
Andy
December 2, 2003 at 5:37 pm
Thanks anyway Andy. This site has beeen my right hand for quite sometime and I am always grateful for each issue I have had and all the answers and suggestions received in the past. I will keep on coming in for reasearch, questions and tech support.
Cordially
Carlos F. Martinez
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply