If statement in Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

If statement in Insert

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-04-26 : 13:33:10

Below is not working:

CREATE PROCEDURE dbo.sp_InsertProgramTemp
@SysOrgSID VarChar(20),
@programContact VarChar(100),
@ProgramName VarChar(100),
@ProgramCIP VarChar(10),
@ProgramAward VarChar(10),
@ProgramImpTerm VarChar(10),
@ProgramImpYear VarChar(4),
@ProgramEdFlag VarChar(1),
@programBriefSum VarChar(2000),
@programAppropriateness VarChar(2000),
@programIntellectualValue VarChar(2000),
@programLongtermStudentDemand VarChar(2000),
@programRegionalMarketDemand VarChar(2000),
@programNationalInternational VarChar(2000),
@programOtherStateSystem VarChar(2000),
@programAddedValue VarChar(2000),
@programAcademicIntegrity VarChar(2000),
@programCoordination VarChar(2000),
@programAssessment VarChar(2000),
@programResourceSufficiency VarChar(2000),
@programImpact VarChar(2000),
@lastFormID int,
@programSubmitFlagID int,
@programStatusID int

As
declare @ID int
SET NOCOUNT ON
INSERT INTO
dbo.tblACRDIMProgramTemp
(
SysOrgSID,
programContact,
ProgramName,
ProgramCIP,
ProgramAward,
ProgramImpTerm,
ProgramImpYear,
ProgramEdFlag,
lastFormID,
programSubmitFlag

)
VALUES
(
@SysOrgSID,
@programContact,
@ProgramName,
@ProgramCIP,
@ProgramAward,
@ProgramImpTerm,
@ProgramImpYear,
@ProgramEdFlag,
@lastFormID,
@programSubmitFlagID

)
SELECT @ID = SCOPE_IDENTITY()

INSERT INTO dbo.tblACRDIMProgramPending
(
ProgramSID,
ProgramPendingDate,
ProgramLOIReceived,
ProgramPendingDesc,
programBriefSum,
programAppropriateness,
programIntellectualValue,
programLongtermStudentDemand,
programRegionalMarketDemand,
programNationalInternational,
programOtherStateSystem,
programAddedValue,
programAcademicIntegrity,
programCoordination,
programAssessment,
programResourceSufficiency,
programImpact,
programStatusID
)
VALUES
(
@ID,
GETDATE(),
if @programStatusID = 4
GETDATE(),
ELSE
NULL,
END,
'none',
@programBriefSum,
@programAppropriateness,
@programIntellectualValue,
@programLongtermStudentDemand,
@programRegionalMarketDemand,
@programNationalInternational,
@programOtherStateSystem,
@programAddedValue,
@programAcademicIntegrity,
@programCoordination,
@programAssessment,
@programResourceSufficiency,
@programImpact,
@programStatusID
)
GO

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-04-26 : 13:34:26

I figured it out:

Case @programStatusID when 4 Then
GETDATE()
ELSE
NULL
END,

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-04-26 : 14:15:03

But now I am stuck on the update

CREATE PROCEDURE dbo.sp_UpdateProgramTemp
@ProgramPendingID int,
@ProgramSID int,
@programContact VarChar(100),
@ProgramName VarChar(100),
@ProgramCIP VarChar(10),
@ProgramAward VarChar(10),
@ProgramImpTerm VarChar(10),
@ProgramImpYear VarChar(4),
@ProgramEdFlag VarChar(1),
@programBriefSum VarChar(2000),
@programAppropriateness VarChar(2000),
@programIntellectualValue VarChar(2000),
@programLongtermStudentDemand VarChar(2000),
@programRegionalMarketDemand VarChar(2000),
@programNationalInternational VarChar(2000),
@programOtherStateSystem VarChar(2000),
@programAddedValue VarChar(2000),
@programAcademicIntegrity VarChar(2000),
@programCoordination VarChar(2000),
@programAssessment VarChar(2000),
@programResourceSufficiency VarChar(2000),
@programImpact VarChar(2000),
@lastFormID int,
@programSubmitFlagID int,
@programStatusID int

As
declare @ProgramLOIReceivedVal datetime
if @programStatusID = 4
set @ProgramLOIReceivedVal = GETDATE()
ELSE
set @ProgramLOIReceivedVal = NULL
END

Update
dbo.tblACRDIMProgramTemp
SET
ProgramContact = @programContact,
ProgramName = @ProgramName,
ProgramCIP = @ProgramCIP,
ProgramAward = @ProgramAward,
ProgramImpTerm = @ProgramImpTerm,
ProgramImpYear = @ProgramImpYear,
ProgramEdFlag = @ProgramEdFlag,
lastFormID = @lastFormID,
programSubmitFlag = @programSubmitFlagID
Where
ProgramSID = @ProgramSID

Update dbo.tblACRDIMProgramPending
SET
ProgramPendingDate = GETDATE(),
ProgramLOIReceived = GETDATE(),
ProgramPendingDesc = 'none',
programBriefSum = @programBriefSum,
programAppropriateness = @programAppropriateness,
programIntellectualValue = @programIntellectualValue,
programLongtermStudentDemand = @programLongtermStudentDemand,
programRegionalMarketDemand = @programRegionalMarketDemand,
programNationalInternational = @programNationalInternational,
programOtherStateSystem = @programOtherStateSystem,
programAddedValue = @programAddedValue,
programAcademicIntegrity = @programAcademicIntegrity,
programCoordination = @programCoordination,
programAssessment = @programAssessment,
programResourceSufficiency = @programResourceSufficiency,
programImpact = @programImpact,
programStatusID = @programStatusID
WHERE
ProgramPendingID = @ProgramPendingID
GO

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 14:34:20

I cant see where this @ProgramLOIReceivedVal is actually used in your update

But you can try this syntax for your if statement


declare @ProgramLOIReceivedVal datetime
if @programStatusID = 4
BEGIN
set @ProgramLOIReceivedVal = GETDATE()
END
ELSE
BEGIN
set @ProgramLOIReceivedVal = NULL
END

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources