By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Insert from parameters and select statement

P: n/a
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

May 30 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(se****************@lmco.com) writes:
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:


Try:

INSERT tbl (col1, col2, col3, ....)
SELECT @par1, @par2, col
FROM ESBAOffsets..Offsets_Master
WHERE RECID = @RECID

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.