Problem with Insert Statement 1

[cmarch1]

I've tried several different ways to do a comparison in an insert clause...can anyone assist me?.....here is some of the code (need help with code in quotations):

INSERT INTO Grants
(
PlanID, PrevGrantID, ProgramID, GrantStatusID,
IA_GrantPrjDirID, IA_GrantPrjDirSrcID, GrantPrjDirName,
DateCreated, RequestDate, BudgetDate, BudgetAmount
)
SELECT
PlanID, GrantID, ProgramID, 'B',
IA_GrantPrjDirID, IA_GrantPrjDirSrcID, GrantPrjDirName,
GetDate(), DateAdd (yyyy,1, RequestDate), @NewBudgetDate,
"Case GrantAmount
when NULL then BudgetAmount
Else GrantAmount
End"
FROM vwRollover_Grants
WHERE (((FiscalYear)= @pintPrevFY + 1 ))


The "Case" statement is the only thing I've created not giving me an error in this stored procedure. I need to insert a value based on what is stored in the Grant Amount field. If its null then I need to use the Budget Amount.

 

[rajeevnandanmishra]

Hi there,
Will you please let us know that what error you are getting by executing the above sql statements.

 

[aalmeida]

cmarch1!
I think ib this case you would be better off using the system function isnull like this:
INSERT INTO Grants(
PlanID,
PrevGrantID,
ProgramID,
GrantStatusID,
IA_GrantPrjDirID,
IA_GrantPrjDirSrcID,
GrantPrjDirName,
DateCreated,
RequestDate,
BudgetDate,
BudgetAmount
)
SELECT
PlanID,
GrantID,
ProgramID,
'B',
IA_GrantPrjDirID,
IA_GrantPrjDirSrcID,
GrantPrjDirName,
GetDate(),
DateAdd (yyyy,1, RequestDate),
@NewBudgetDate,
isnull(GrantAmount, BudgetAmount)
FROM vwRollover_Grants
WHERE (((FiscalYear)= @pintPrevFY + 1 ))

But if you really want to use a case statment, you have to remember that comparisons with null values depends on your server\connection configuration, for me this one works just fine.
INSERT INTO Grants(
PlanID,
PrevGrantID,
ProgramID,
GrantStatusID,
IA_GrantPrjDirID,
IA_GrantPrjDirSrcID,
GrantPrjDirName,
DateCreated,
RequestDate,
BudgetDate,
BudgetAmount
)
SELECT
PlanID,
GrantID,
ProgramID,
'B',
IA_GrantPrjDirID,
IA_GrantPrjDirSrcID,
GrantPrjDirName,
GetDate(),
DateAdd (yyyy,1, RequestDate),
@NewBudgetDate,
Case when GrantAmount = NULL then BudgetAmount Else GrantAmount End
FROM vwRollover_Grants
WHERE (((FiscalYear)= @pintPrevFY + 1 ))

I hope I could help you!
aalmeida

 

[cmarch1]

to the first reply....I get all the GrantAmount where there is one but "no" amount where the GrantAmount is null and it should pick up the BudgetAmount

to the second reply...I'll try these now and see what the results are

 

[cmarch1]

aalmeida
Thank you ...the isnull worked....the other Case statement did not though but at least its working...thank you again for your and rajeevnandanmishra's input