/* Demo scripts for WaterOx Consulting Walk & Talk 001 - Unknown Values */

/* Looking at ISNULL() & COALLESCE() */

/* Let's set up some variables, some with values, to use rather than relying on a bunch of tables */
/* We will not define a default value for the second variable so that it will default to a null value */

DECLARE @Var1 INT = 1 ,
    @Var2 INT ,
    @Var3 INT = 3

SELECT  @Var1 AS V1 ,
        @Var2 AS V2 ,
        @Var3 AS V3 ,
        @Var1 + @Var2 + @Var3 AS Total;

/* Notice how the null value of Var2 results in a NULL Total*/
/* We can work around this problem using the ISNULL statement and assigning a value to the field if it is null */

DECLARE @Var1 INT = 1 ,
    @Var2 INT ,
    @Var3 INT = 3

SELECT  @Var1 AS V1 ,
        @Var2 AS V2 ,
        @Var3 AS V3 ,
        @Var1 + @Var2 + @Var3 AS Old_Total ,
        @Var1 + ISNULL(@Var2, 2) + @Var3 AS Total;

/* HINT - This same INSULL can be used for various datatypes to ensure you get the results you expect */

/* Let's add some more variables and look at the COALESCE function */
/* We will not add default values to the first couple, so they will be null */

DECLARE @Var1 INT ,
    @Var2 INT ,
    @Var3 INT = 3 ,
    @Var4 INT ,
    @Var5 INT

SELECT  @Var1 AS V1 ,
        @Var2 AS V2 ,
        @Var3 AS V3 ,
        @Var4 AS V4 ,
        @Var5 AS V5 ,
        COALESCE(@var1, @var2, @var3, @var4, @var5, 99) AS First_Non_Null;


/* If we were to remove the value for all variables, you will see the final value in the coallesce array is returned */
DECLARE @Var1 INT ,
    @Var2 INT ,
    @Var3 INT ,
    @Var4 INT ,
    @Var5 INT

SELECT  @Var1 AS V1 ,
        @Var2 AS V2 ,
        @Var3 AS V3 ,
        @Var4 AS V4 ,
        @Var5 AS V5 ,
        COALESCE(@var1, @var2, @var3, @var4, @var5, 99) AS First_Non_Null;