July 27, 2010 at 7:31 am
I have a stored procedure that I need to add a subinsert to it. Not having done this before, I am unsure how to proceed. Googling did not help.
Here is the current SP;
USE [BE]
GO
/****** Object: StoredProcedure [dbo].[NewProject] Script Date: 07/27/2010 13:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewProject]
@ReviewStatus nvarchar (50),
@status nvarchar (50),
@PrepBy nvarchar (50),
@Type nvarchar (255),
@ProjectYear nvarchar (50),
@Priority nvarchar(255),
@ProjectType nvarchar (2),
@ProjectNum09 nvarchar (50),
@AFE09 nvarchar (50),
@AFE nvarchar (255),
@ProjectName nvarchar (255),
@ProjectCust nvarchar (255),
@FundingSrcDesc nvarchar (255),
@DirectorID int,
@ManagerID int,
@ProjectMgrID int,
@ProjectObj nvarchar (MAX),
@BusinessCase nvarchar (MAX),
@CustNeeds nvarchar (MAX),
@FinalDeliverables nvarchar (MAX),
@SchLaborDys nvarchar (50),
@ExemptLaborDys nvarchar (50),
@SchLaborCost nvarchar (50),
@EstMatCost nvarchar (50),
@ContrLaborCost nvarchar (50),
@OtherCost nvarchar (50),
@TotProjCost nvarchar (50),
@StartDate datetime,
@EstDueDate datetime,
@ApprovalNotes nvarchar (255),
@ProjectReviewID int,
@EngSME nvarchar (255),
@RequestDate datetime,
@SchPriority nvarchar (50),
@ScopePriority nvarchar (50),
@CostPriority nvarchar (50),
@AllocLaborCost nvarchar (50),
@AllocLaborHrs nvarchar (50),
@AllocMatCost nvarchar (50),
@TotActualCost nvarchar (50),
@FundingQtr nvarchar (50),
@2QAFEAmt nvarchar (50),
@3QTarget nvarchar (50),
@4QTarget nvarchar (50),
@DateModified datetime,
@ModifiedBy nvarchar (50)
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO [2010Projects]
(ReviewStatus,
Status,
PrepBy,
Type,
ProjectYear,
Priority,
ProjectType,
ProjectNum09,
AFE09,
AFE,
ProjectName,
ProjectCust,
FundingSrcDesc,
DirectorID,
ManagerID,
ProjectMgrID,
ProjectObj,
BusinessCase,
CustNeeds,
FinalDeliverables,
SchLaborDys,
ExemptLaborDys,
SchLaborCost,
EstMatCost,
ContrLaborCost,
OtherCost,
TotProjCost,
StartDate,
EstDueDate,
ApprovalNotes,
ProjectReviewID,
EngSME,
RequestDate,
SchPriority,
ScopePriority,
CostPriority,
AllocLaborCost,
AllocLaborHrs,
AllocMatCost,
TotActualCost,
FundingQtr,
[2QAFEAmt],
[3QTarget],
[4QTarget],
DateModified,
ModifiedBy)
VALUES
(@ReviewStatus,
@PrepBy,
@Type,
@ProjectYear,
@Priority,
@ProjectType,
@ProjectNum09,
@AFE09,
@AFE,
@ProjectName,
@ProjectCust,
@FundingSrcDesc,
@DirectorID,
@ManagerID,
@ProjectMgrID,
@ProjectObj,
@BusinessCase,
@CustNeeds,
@FinalDeliverables,
@SchLaborDys,
@ExemptLaborDys,
@SchLaborCost,
@EstMatCost,
@ContrLaborCost,
@OtherCost,
@TotProjCost,
@StartDate,
@EstDueDate,
@ApprovalNotes,
@ProjectReviewID,
@EngSME,
@RequestDate,
@SchPriority,
@ScopePriority,
@CostPriority,
@AllocLaborCost,
@AllocLaborHrs,
@AllocMatCost,
@TotActualCost,
@FundingQtr,
@2QAFEAmt,
@3QTarget,
@4QTarget,
@DateModified,
@ModifiedBy)
END
And here is the insert for the query I need to add;
INSERT INTO [BE].[dbo].[Resources]
([ResourceID]
,[ProjectID]
,[ResourceType]
,[North]
,[Central]
,[South]
,[NetPlan]
,[NetImpl]
,[NetSup]
,[TSOC]
,[CapMgmt]
,[TrnsNet]
,[Voice]
,[CRF]
,[TC]
,[TOther]
,[DataCenter]
,[DisasterRec]
,[EntSec]
,[LANOps]
,[UserReg]
,[MainfOps]
,[IGS]
,[NTOther])
VALUES
(<ResourceID, int,>
,<ProjectID, int,>
,<ResourceType, nvarchar(100),>
,<North, bit,>
,<Central, bit,>
,<South, bit,>
,<NetPlan, bit,>
,<NetImpl, bit,>
,<NetSup, bit,>
,<TSOC, bit,>
,<CapMgmt, bit,>
,<TrnsNet, bit,>
,<Voice, bit,>
,<CRF, bit,>
,<TC, bit,>
,<TOther, nvarchar(50),>
,<DataCenter, bit,>
,<DisasterRec, bit,>
,<EntSec, bit,>
,<LANOps, bit,>
,<UserReg, bit,>
,<MainfOps, bit,>
,<IGS, bit,>
,<NTOther, nvarchar(50),>)
GO
The Resources insert, uses the ProjectID from the 2010Projects table as the reference. How should I do this?
Thanks,
Brian
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 27, 2010 at 7:35 am
Try this buddy!
INSERT INTO [BE].[dbo].[Resources]
([ResourceID]
,[ProjectID]
,[ResourceType]
,[North]
,[Central]
,[South]
,[NetPlan]
,[NetImpl]
,[NetSup]
,[TSOC]
,[CapMgmt]
,[TrnsNet]
,[Voice]
,[CRF]
,[TC]
,[TOther]
,[DataCenter]
,[DisasterRec]
,[EntSec]
,[LANOps]
,[UserReg]
,[MainfOps]
,[IGS]
,[NTOther])
SELECT
[ResourceID]
,@ProjectID
,[ResourceType]
,[North]
,[Central]
,[South]
,[NetPlan]
,[NetImpl]
,[NetSup]
,[TSOC]
,[CapMgmt]
,[TrnsNet]
,[Voice]
,[CRF]
,[TC]
,[TOther]
,[DataCenter]
,[DisasterRec]
,[EntSec]
,[LANOps]
,[UserReg]
,[MainfOps]
,[IGS]
,[NTOther]
FROM
dbo.2010Projects
WHERE
ProjectID = @ProjectID
July 27, 2010 at 8:40 am
So if I add a subquery to the Select statement that has this;
SELECT TOP 1 [ProjectID]
FROM [BE].[dbo].[2010Projects]
ORDER BY [ProjectID] DESC
That should give me the latest ProjectID number so that the tables match up yes?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 27, 2010 at 10:40 am
Yes ,Brian, right.. but there are better solutions to get teh latest ProjectID.. if you would provide us some sample data, your table structure, then we would happily solve your issue..
July 27, 2010 at 10:50 am
ColdCoffee (7/27/2010)
Yes ,Brian, right.. but there are better solutions to get teh latest ProjectID.. if you would provide us some sample data, your table structure, then we would happily solve your issue..
Here is a sample of the 2010Projects table;
ProjectIDReviewStatusStatusPrepByTypeProjectYearPriorityProjectTypeProjectNum09AFE09AFEProjectNameProjectCustFundingSrcDescDirectorIDManagerIDProjectMgrIDProjectObjBusinessCaseCustNeedsFinalDeliverablesSchLaborDysExemptLaborDysSchLaborCostEstMatCostContrLaborCostOtherCostTotProjCostStartDateEstDueDateApprovalNotesProjectReviewIDEngSMERequestDateSchPriorityScopePriorityCostPriorityAllocLaborCostAllocLaborHrsAllocMatCostTotActualCostFundingQtr2QAFEAmt3QTarget4QTargetDateModifiedModifiedBy
1NULLFundedNULLD20103 CentralYIYI015TBDA10-4835Denver Mechanical 1 Spot Building Fiber Phase IMechanicalTelecom AFE11428Provide a fiber connection between the Denver Node and the Mechanical 1 Spot building for WAN & telephone connectivinty, two yard monitor cameras and radio systems. Eliminating PairGain transmission systems.40 year old copper cable system does not support todays network requirements & cable pairs are limitedReliable fiber transport of voice and data4000' feet of directional bore / trench, fiber, Cisco switches5353710013652000173620NULL2009-12-31 00:00:00.0002/2/10 Project approved for funding for 2Q per Sue Borsellino36TBD2008-09-04 00:00:00.000NULLNULLNULL37100013652002QNULLNULLNULL2010-02-03 00:00:00.000B175042
Here is the 2010Projects table structure
USE [BE]
GO
/****** Object: Table [dbo].[2010Projects] Script Date: 07/27/2010 16:48:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[2010Projects](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[ReviewStatus] [nvarchar](50) NULL,
[Status] [nvarchar](50) NULL,
[PrepBy] [nvarchar](50) NULL,
[Type] [nvarchar](255) NULL,
[ProjectYear] [nvarchar](50) NULL,
[Priority] [nvarchar](255) NULL,
[ProjectType] [nvarchar](2) NULL,
[ProjectNum09] [nvarchar](50) NULL,
[AFE09] [nvarchar](50) NULL,
[AFE] [nvarchar](255) NULL,
[ProjectName] [nvarchar](255) NULL,
[ProjectCust] [nvarchar](255) NULL,
[FundingSrcDesc] [nvarchar](255) NULL,
[DirectorID] [int] NULL,
[ManagerID] [int] NULL,
[ProjectMgrID] [int] NULL,
[ProjectObj] [nvarchar](max) NULL,
[BusinessCase] [nvarchar](max) NULL,
[CustNeeds] [nvarchar](max) NULL,
[FinalDeliverables] [nvarchar](max) NULL,
[SchLaborDys] [nvarchar](50) NULL,
[ExemptLaborDys] [nvarchar](50) NULL,
[SchLaborCost] [nvarchar](50) NULL,
[EstMatCost] [nvarchar](50) NULL,
[ContrLaborCost] [nvarchar](50) NULL,
[OtherCost] [nvarchar](50) NULL,
[TotProjCost] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[EstDueDate] [datetime] NULL,
[ApprovalNotes] [nvarchar](255) NULL,
[ProjectReviewID] [int] NULL,
[EngSME] [nvarchar](255) NULL,
[RequestDate] [datetime] NULL,
[SchPriority] [nvarchar](50) NULL,
[ScopePriority] [nvarchar](50) NULL,
[CostPriority] [nvarchar](50) NULL,
[AllocLaborCost] [nvarchar](50) NULL,
[AllocLaborHrs] [nvarchar](50) NULL,
[AllocMatCost] [nvarchar](50) NULL,
[TotActualCost] [nvarchar](50) NULL,
[FundingQtr] [nvarchar](50) NULL,
[2QAFEAmt] [nvarchar](50) NULL,
[3QTarget] [nvarchar](50) NULL,
[4QTarget] [nvarchar](50) NULL,
[DateModified] [datetime] NULL,
[ModifiedBy] [nvarchar](50) NULL,
CONSTRAINT [PK_2010Projects] PRIMARY KEY CLUSTERED
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is a sample from the Resources table;
ResourceIDProjectIDResourceTypeNorthCentralSouthNetPlanNetImplNetSupTSOCCapMgmtTrnsNetVoiceCRFTCTOtherDataCenterDisasterRecEntSecLANOpsUserRegMainfOpsIGSNTOther
11NULL000000000000NULL0000000NULL
And lastly here is the Resources table structure;
USE [BE]
GO
/****** Object: Table [dbo].[Resources] Script Date: 07/27/2010 16:50:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Resources](
[ResourceID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NULL,
[ResourceType] [nvarchar](100) NULL,
[North] [bit] NOT NULL,
[Central] [bit] NOT NULL,
[South] [bit] NOT NULL,
[NetPlan] [bit] NOT NULL,
[NetImpl] [bit] NOT NULL,
[NetSup] [bit] NOT NULL,
[TSOC] [bit] NOT NULL,
[CapMgmt] [bit] NOT NULL,
[TrnsNet] [bit] NOT NULL,
[Voice] [bit] NOT NULL,
[CRF] [bit] NOT NULL,
[TC] [bit] NOT NULL,
[TOther] [nvarchar](50) NULL,
[DataCenter] [bit] NOT NULL,
[DisasterRec] [bit] NOT NULL,
[EntSec] [bit] NOT NULL,
[LANOps] [bit] NOT NULL,
[UserReg] [bit] NOT NULL,
[MainfOps] [bit] NOT NULL,
[IGS] [bit] NOT NULL,
[NTOther] [nvarchar](50) NULL,
CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED
(
[ResourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks for the help.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 27, 2010 at 11:35 am
brian.cook (7/27/2010)
So if I add a subquery to the Select statement that has this;SELECT TOP 1 [ProjectID]
FROM [BE].[dbo].[2010Projects]
ORDER BY [ProjectID] DESC
That should give me the latest ProjectID number so that the tables match up yes?
Immediately after you do the insert into the table that generates your ProjectID, set an int variable = SCOPE_IDENTITY(). That variable now has the value of the ProjectID if it's an IDENTITY and you can use that to insert into the other table.
July 27, 2010 at 1:43 pm
bteraberry (7/27/2010)
brian.cook (7/27/2010)
So if I add a subquery to the Select statement that has this;SELECT TOP 1 [ProjectID]
FROM [BE].[dbo].[2010Projects]
ORDER BY [ProjectID] DESC
That should give me the latest ProjectID number so that the tables match up yes?
Immediately after you do the insert into the table that generates your ProjectID, set an int variable = SCOPE_IDENTITY(). That variable now has the value of the ProjectID if it's an IDENTITY and you can use that to insert into the other table.
That is an interesting function. I was not aware of that one.
If I am automatically generating the ProjectID and it is not part of my INSERT statement, how can I set it as a SCOPE_IDENTITY variable? Use the above query? Declare it as an OUTPUT variable so that my original INSERT looks like this;
USE [BE]
GO
/****** Object: StoredProcedure [dbo].[NewProject] Script Date: 07/27/2010 19:06:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewProject]
@ProjectID INT OUTPUT,
@ReviewStatus nvarchar (50),
@status nvarchar (50),
@PrepBy nvarchar (50),
@Type nvarchar (255),
@ProjectYear nvarchar (50),
@Priority nvarchar(255),
@ProjectType nvarchar (2),
@ProjectNum09 nvarchar (50),
@AFE09 nvarchar (50),
@AFE nvarchar (255),
@ProjectName nvarchar (255),
@ProjectCust nvarchar (255),
@FundingSrcDesc nvarchar (255),
@DirectorID int,
@ManagerID int,
@ProjectMgrID int,
@ProjectObj nvarchar (MAX),
@BusinessCase nvarchar (MAX),
@CustNeeds nvarchar (MAX),
@FinalDeliverables nvarchar (MAX),
@SchLaborDys nvarchar (50),
@ExemptLaborDys nvarchar (50),
@SchLaborCost nvarchar (50),
@EstMatCost nvarchar (50),
@ContrLaborCost nvarchar (50),
@OtherCost nvarchar (50),
@TotProjCost nvarchar (50),
@StartDate datetime,
@EstDueDate datetime,
@ApprovalNotes nvarchar (255),
@ProjectReviewID int,
@EngSME nvarchar (255),
@RequestDate datetime,
@SchPriority nvarchar (50),
@ScopePriority nvarchar (50),
@CostPriority nvarchar (50),
@AllocLaborCost nvarchar (50),
@AllocLaborHrs nvarchar (50),
@AllocMatCost nvarchar (50),
@TotActualCost nvarchar (50),
@FundingQtr nvarchar (50),
@2QAFEAmt nvarchar (50),
@3QTarget nvarchar (50),
@4QTarget nvarchar (50),
@DateModified datetime,
@ModifiedBy nvarchar (50)
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO [2010Projects]
(ReviewStatus,
Status,
PrepBy,
Type,
ProjectYear,
Priority,
ProjectType,
ProjectNum09,
AFE09,
AFE,
ProjectName,
ProjectCust,
FundingSrcDesc,
DirectorID,
ManagerID,
ProjectMgrID,
ProjectObj,
BusinessCase,
CustNeeds,
FinalDeliverables,
SchLaborDys,
ExemptLaborDys,
SchLaborCost,
EstMatCost,
ContrLaborCost,
OtherCost,
TotProjCost,
StartDate,
EstDueDate,
ApprovalNotes,
ProjectReviewID,
EngSME,
RequestDate,
SchPriority,
ScopePriority,
CostPriority,
AllocLaborCost,
AllocLaborHrs,
AllocMatCost,
TotActualCost,
FundingQtr,
[2QAFEAmt],
[3QTarget],
[4QTarget],
DateModified,
ModifiedBy)
VALUES
(@ReviewStatus,
@PrepBy,
@Type,
@ProjectYear,
@Priority,
@ProjectType,
@ProjectNum09,
@AFE09,
@AFE,
@ProjectName,
@ProjectCust,
@FundingSrcDesc,
@DirectorID,
@ManagerID,
@ProjectMgrID,
@ProjectObj,
@BusinessCase,
@CustNeeds,
@FinalDeliverables,
@SchLaborDys,
@ExemptLaborDys,
@SchLaborCost,
@EstMatCost,
@ContrLaborCost,
@OtherCost,
@TotProjCost,
@StartDate,
@EstDueDate,
@ApprovalNotes,
@ProjectReviewID,
@EngSME,
@RequestDate,
@SchPriority,
@ScopePriority,
@CostPriority,
@AllocLaborCost,
@AllocLaborHrs,
@AllocMatCost,
@TotActualCost,
@FundingQtr,
@2QAFEAmt,
@3QTarget,
@4QTarget,
@DateModified,
@ModifiedBy)
END
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 27, 2010 at 2:01 pm
No. Do your insert as normal without reference to the ID column. The insert will create the row and generate the identity. The very next statement after the insert should take a previously declared variable and set it equal to SCOPE_IDENTITY:
DECLARE @ID INT
INSERT table (Desc) VALUES ('sample')
SELECT @ID = SCOPE_IDENTITY()
From there you can use the @ID variable to access the identity that was created. This is a system function that SQL supports under the hood and all you have to do is read the output.
July 27, 2010 at 2:18 pm
jeff.mason (7/27/2010)
No. Do your insert as normal without reference to the ID column. The insert will create the row and generate the identity. The very next statement after the insert should take a previously declared variable and set it equal to SCOPE_IDENTITY:DECLARE @ID INT
INSERT table (Desc) VALUES ('sample')
SELECT @ID = SCOPE_IDENTITY()
From there you can use the @ID variable to access the identity that was created. This is a system function that SQL supports under the hood and all you have to do is read the output.
And do I put this after the END statement of the original SP? or before it?
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 27, 2010 at 2:22 pm
The SELECT @variable = SCOPE_IDENTITY needs to be the very next statement after the insert to ensure you are getting the proper IDENTITY. So before the END.
July 27, 2010 at 2:24 pm
jeff.mason (7/27/2010)
The SELECT @variable = SCOPE_IDENTITY needs to be the very next statement after the insert to ensure you are getting the proper IDENTITY. So before the END.
Gotcha.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply