|
|
Posted by serena.delossantos on 05/30/06 17:48
Trying to insert into a history table. Some columns will come from
parameters sent to the store procedure. Other columns will be filled
with a separate select statement. I've tried storing the select return
in a cursor, tried setting the values for each field with a separate
select. Think I've just got the syntax wrong. Here's one of my
attempts:
use ESBAOffsets
go
if exists
(select * from sysobjects where name='InsertOffsetHistory' and
type='P')
drop procedure InsertOffsetHistory
go
create procedure dbo.InsertOffsetHistory
@RECID int,
@LOB int,
@PRODUCT int,
@ITEM_DESC varchar(100),
@AWARD_DATE datetime,
@CONTRACT_VALUE float,
@PROG_CONT_STATUS int,
@CONTRACT_NUMBER varchar(25),
@WA_OD varchar(9),
@CURR_OFFSET_OBL float,
@DIRECT_OBL float,
@INDIRECT_OBL float,
@APPROVED_DIRECT float,
@APPROVED_INDIRECT float,
@CREDITS_INPROC_DIRECT float,
@CURR_INPROC_INDIRECT float,
@OBLIGATION_REMARKS varchar(5000),
@TRANSACTION_DATE datetime,
@AUTH_USER varchar(150),
@AUTHUSER_LNAME varchar(150)
as
declare @id int
insert into ESBAOffsets..HISTORY
(RECID,
COID,
SITEID,
LOB,
COUNTRY,
PRODUCT,
ITEM_DESC,
AWARD_DATE,
CONTRACT_VALUE,
PROG_CONT_STATUS,
CONTRACT_TYPE,
FUNDING_TYPE,
CONTRACT_NUMBER,
WA_OD,
PM,
AGREEMENT_NUMBER,
CURR_OFFSET_OBL,
DIRECT_OBL,
INDIRECT_OBL,
APPROVED_DIRECT,
APPROVED_INDIRECT,
CREDITS_INPROC_DIRECT,
CURR_INPROC_INDIRECT,
PERF_PERIOD,
REQ_COMP_DATE,
PERF_MILESTONE,
TYPE_PENALTY,
PERF_GUARANTEE,
PENALTY_RATE,
STARTING_PENALTY,
PENALTY_EXCEPTION,
CORP_GUARANTEE,
BANK,
RISK,
REMARKS,
OBLIGATION_REMARKS,
MILESTONE_REMARKS,
NONSTANDARD_REMARKS,
TRANSACTION_DATE,
STATUS,
AUTH_USER,
PMLNAME,
EXLD_PROJ,
COMPLDATE,
AUTHUSER_LNAME)
values
(@RECID,
(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),
(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),
@LOB,
(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =
@RECID),
@PRODUCT,
@ITEM_DESC,
@AWARD_DATE,
@CONTRACT_VALUE,
@PROG_CONT_STATUS,
(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
@CONTRACT_NUMBER,
@WA_OD,
(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),
(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID
= @RECID),
@CURR_OFFSET_OBL,
@DIRECT_OBL,
@INDIRECT_OBL,
@APPROVED_DIRECT,
@APPROVED_INDIRECT,
@CREDITS_INPROC_DIRECT,
@CURR_INPROC_INDIRECT,
(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID
= @RECID),
(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID
= @RECID),
(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),
(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),
(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master where
RECID = @RECID),
@MILESTONE_REMARKS,
@NONSTANDARD_REMARKS,
@TRANSACTION_DATE,
(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),
@AUTH_USER,
(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =
@RECID),
(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =
@RECID),
@AUTHUSER_LNAME)
select @@identity id
go
grant execute on InsertOffsetHistory to public
go
Navigation:
[Reply to this message]
|