Referencing a database in if statements

Question

I am currently running the below code through SQL Server Management Studio queries before I implement it in my Django-Python application.

I am having trouble figuring out how to reference the database in question before the if statement begins:

Query:

If Age1 IS NULL 
    set @Current = DCBalance
    Select Account, Name, E-mail , Age1 , Age2 , Age3 , Age4 , Age5 , @Current ,DCBalance from [Graceland Estates BC].[dbo].[_avCustomerAging]
Else  Age1 IS NOT NULL
    If UADebit <> '0' and DCBalance > 0 
        Set @Current = DCBal - Sum(Age1, Age2, Age3 , Age4, Age5)
        Select Account , Name , E-mail , Age1 , Age2 , Age3 , Age4 , Age5 , @Current , DCBalance from [Graceland Estates BC].[dbo].[_avCustomerAging]
    Else  UADebit = '0'  and DCBalance < 0 
        Set @Age1X = fForeignBalance + Age1 - FCAge1
        Set @Age2X = fForeignBalance + Age2 - FCAge2
        Set @Age3X = fForeignBalance + Age3 - FCAge3
        Set @Age4X = fForeignBalance + Age4 - FCAge4
        Set @Age5X = fForeignBalance + Age5 - FCAge5
        Set @Current = 0
        Select Account , Name , E-mail , @Age1X , @Age2X , @Age3X , @Age4X , @Age5X , @Current , DCBalance
    Else  UADebit = '0'  and DCBalance > 0 
        Set @Ages = '0'
        Select Account , Name , E-mail , @Ages , @Ages , @Ages , @Ages , @Ages , DCBalance as Current , DCBalance

In the above code, it is meant to determine if the data matches what is represented in the if statement and do a calculation based on the way the data is displayed.

Here is the code when I try the same thing with a CASE expression:

Select Account, Name , EMail, Age1 AS Cur, Age2 , Age3 , Age4, Age5 ,DCBalance,
Case 
    When AccountLink = NULL Then Age1 = DCBalance and Age2 = 0 and Age3 = 0 and Age4 = 0 and Age5= 0
    When UADebit <> 0 and DCBalance > 0 Then Age1 = DCBalance - (Age2 + Age3 + Age4 + Age5)
    When UADebit = 0 and DCBalance < 0 Then Age1 = 0 and Age2 = fForeignBalance + Age2 - FCAge2 and Age3 = fForeignBalance + Age3 - FCAge3 and Age4 = fForeignBalance + Age4 - FCAge4 and Age5 = fForeignBalance + Age5 - FCAge5
    When UADebit = 0 and DCBalance > 0 Then Age1 = DCBalance and Age2 = 0 and Age3 = 0 and Age4 = 0 and Age5= 0
End 
From [Kyle].[dbo].[_avCustomerAging]

The error:

Incorrect syntax near '='.

is returned when this is executed

What does “reference the database” mean? Perhaps the [`Initial Catalog`](https://stackoverflow.com/questions/1949774/what-is-the-point-of-initial-catalog-in-a-sql-server-connection-string) connection string parameter might be useful? However, `If Age1 ..` will always be invalid. — user2864740, Oct 27 '21 at 06:38
@user2864740 - How would I be able to make `If Age1 ...` valid in this case. I would Obviously need to tell query which database to look in to find this Age1 Variable I have tried to execute this with `If [Graceland Estates BC].[dbo].[_avCustomerAging].[Age1] IS NULL ` however this is obviously still not correct — Kyle, Oct 27 '21 at 06:49
@DaleK, I have included my code for the case statement I the question, however I still receive a `Syntax Error` for this set of code — Kyle, Oct 27 '21 at 07:34

score 2 · Accepted Answer · answered Oct 27 '21 at 07:46

CASE is an expression NOT a statement, which is made clear when referencing the official documentation i.e. a single case expression, regardless of number of branches, can return a single value. It does not allow you to run conditional parts of your query.

In addition you cannot compare NULL with = you use IS NULL.

I think the following contains the same logic you are aiming for, but it should trivial to tweak it now that you can see where you were going wrong.

SELECT Account, [Name], EMail, Age1, Age2, Age3, Age4, Age5, DCBalance,

    CASE 
        WHEN AccountLink IS NULL THEN DCBalance
        WHEN UADebit <> 0 AND DCBalance > 0 THEN DCBalance - (Age2 + Age3 + Age4 + Age5)
        WHEN UADebit = 0 AND DCBalance < 0 THEN 0
        WHEN UADebit = 0 AND DCBalance > 0 THEN DCBalance
    END AS Age1_New
    ,
    Case 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance + Age2 - FCAge2
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age2_New
    ,
    CASE 
        WHEN AccountLink IS NULL Then 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance + Age3 - FCAge3
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age3_New
    ,
    CASE 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance + Age4 - FCAge4
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age4_New
    ,
    CASE 
        WHEN AccountLink IS NULL THEN 0
        WHEN UADebit = 0 AND DCBalance < 0 THEN fForeignBalance + Age5 - FCAge5
        WHEN UADebit = 0 AND DCBalance > 0 THEN 0
    END Age5_New

FROM _avCustomerAging;

Referencing a database in if statements

1 Answers1