March 5, 2007 at 8:07 am
Hi,
i need help on this
Name of Stored Procedure : SaveProjectPhaseHeader
i have to pass following four Input Parameters
(@MSBA_ID, @Phase_ID, @gsf, @Note)
After that what i want to do is
Insert into COST_Project_Phase table if no records exists or
Update COST_Project_Phase table if record exists.
The structure of the COST_Project_Phase table is as below :
FYI the structure of the table Cost_Project _Phase is as below
USE [Entity]
GO
/****** Object: Table [dbo].[COST_Project] Script Date: 03/04/2007 23:11:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COST_Project_Phase](
[MSBA_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Project_Phase_ID] [int] IDENTITY(1,1) NOT NULL,
[Gross_Square_Footage] [int] NOT NULL,
[Construction_Cost] [int] NULL,
[Soft_Cost] [int] NULL,
[Phase_Note] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_Updated_Dt] [datetime] NULL,
[Last_Updated_By] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [XPKCOST_Project_Phase] PRIMARY KEY NONCLUSTERED
(
[MSBA_ID] ASC,
[Project_Phase_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
March 5, 2007 at 9:22 am
create proc myproc
@MSBA_ID varchar(10),
@Phase_ID int = null,
@GSF int,
@Note varchar(100)
if exists (Select 1 from [dbo].[COST_Project_Phase]
where MSBA_ID = @MSBA_ID and Project_Phase_ID = @Phase_ID)
begin
insert into dbo.[COST_Project_Phase](
[MSBA_ID],
[Gross_Square_Footage] ,
[Phase_Note])
values (@MSBA_ID, @gsf, @Note)
set @Phase_ID = scope_identity()
end
else
begin
update dbo.[COST_Project_Phase]
set [Gross_Square_Footage] = @gsf,
[Phase_Note] = @Note
where MSBA_ID = @MSBA_ID and Project_Phase_ID = @Phase_ID
end
Select * from [dbo].[COST_Project_Phase]
where MSBA_ID = @MSBA_ID and Project_Phase_ID = @Phase_ID
Russel Loski, MCSE Business Intelligence, Data Platform
March 5, 2007 at 9:31 am
thanks for the help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply